Retrieve distinct records question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Retrieve distinct records question

Author

Topic

samdp0817
Starting Member

2 Posts

Posted - 2011-12-07 : 16:20:11

Hello,

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.

TableName: Order_Archive
Fields:
orderid
load_date
filename
order_date
dollar

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 what's 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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 00:42:17

make your upsert logic like below


update t
set t.load_date = s.load_date , 
t.file_name = s.file_name 
from destinationtable t
inner join stagingtable s
on s.orderid = t.orderid 
and s.order_date = t.order_date
and s.dollar = t.dollar 


insert into destinationtable 
select s.*
from stagingtable s
left join destinationtable t
on s.orderid = t.orderid 
and s.order_date = t.order_date
and s.dollar = t.dollar 
where t.orderid is null

staging table is one where you put records directly from file as a temporary storage measure
and destination table is your final table
after each run of package you should be truncating the staging table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

samdp0817
Starting Member

2 Posts

Posted - 2011-12-09 : 12:47:01

Thanks for the advise, I have found out that either of the queries below works:

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

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 Order_Archive)x
where x.RowNum = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 13:03:09

thats fine. but how would you do update/inserts?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources