TableName: Order_Archive
Fields:
orderid
load_date
filename
order_date
dollar



I load a file each week into a table, each file has unique orderid, load_date, filename, order_date and dollar. However the same orderid, order_date and dollar could appear in another file with different load_date and file_name.

File1:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-01', 'File1', '2011-01-01', '102'
'1002', '2011-01-01', 'File1', '2011-01-01', '103'


File2:
orderid, load_date, file_name, order_date, dollar
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'


Question:
Could anyone please advise whats is the best way to retrieve the distinct records
that has the most recent load_date? expected results below:
Expected Results:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'

Thank you in advance!

Not the prettiest but it works:

Select OrderID, Load_Date, File_Name, Order_Date, Dollar
From TABLE a
Where Load_Date = (Select max(Load_Date) From TABLE Where orderid = a.orderid)
Order by 1

I have tried by this . check if this helps.


Create Table dupTest (orderid int, load_date date,file_name varchar(100),Order_date date , doller int)

Insert into dupTest
Select '1000', '2011-01-01', 'File1', '2011-01-01', '101' union all
Select '1001', '2011-01-01', 'File1', '2011-01-01', '102' union all
Select '1002', '2011-01-01', 'File1', '2011-01-01', '103' union all
Select '1001', '2011-01-08', 'File2', '2011-01-01', '102' union all
Select '1002', '2011-01-08', 'File2', '2011-01-01', '103' union all
Select '1003', '2011-01-08', 'File2', '2011-01-01', '104'


Select * from dupTest except
Select a.* from duptest a inner join duptest b on a.orderid=b.orderid and a.order_date=b.order_date where
a.load_date < b.load_date

Thank you for your help! This works. I have also found another solution.


select ORDERID, LOAD_DATE, FILENAME, ORDER_DATE, DOLLAR
from
(
select ORDERID, LOAD_DATE, FILENAME, ORDER_DATE, DOLLAR, ROW_NUMBER() over (partition by OrderID order by LOAD_DATE desc) as RowNum
from TEST_TABLE
)x
where x.RowNum = 1