Retrieve distinct records question

Question

Post reply

Retrieve distinct records question

samdp0817

SSC Enthusiast

Points: 123
More actions
December 7, 2011 at 1:48 pm

#264424

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!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eric SSCommitted Points: 1803 More actions · Answer 1

Can you post the DDL for your table along with any constraints, and reformat your data like it says in this post?

http://www.sqlservercentral.com/articles/Best+Practices/61537/

samdp0817 SSC Enthusiast Points: 123 More actions · Answer 2

Hi, thank you for your response. Please find the below DDL:

CREATE TABLE ORDER_ARCHIVE

(ORDERID INT NOT NULL,

LOAD_DATE DATETIME NOT NULL,

FILENAME VARCHAR(100) NOT NULL,

ORDER_DATE DATETIME NOT NULL,

DOLLAR INT NULL)

--FILE1

INSERT INTO ORDER_ARCHIVE (ORDERID, LOAD_DATE, FILENAME, ORDER_DATE, DOLLAR)

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

--FILE2

INSERT INTO ORDER_ARCHIVE (ORDERID, LOAD_DATE, FILENAME, ORDER_DATE, DOLLAR)

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

samdp0817 SSC Enthusiast Points: 123 More actions · Answer 3

Please be advised that there is no constrains involved. ty

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Something like this should work.

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

--edit-- Added the desc to the order by on Row_Number to get the most recent one instead of the oldest one.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

samdp0817 SSC Enthusiast Points: 123 More actions · Answer 5

samdp0817

SSC Enthusiast

Points: 123

December 7, 2011 at 3:02 pm

#1418390

Thank you very much, I appreciate your help!

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

You're welcome. Definitely makes all the difference when you post ddl and sample data (nicely done by the way).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

Devendrakumar

SSC-Forever

Points: 42493

December 8, 2011 at 12:04 am

#1418491

1260221107 (12/8/2011)
...

Please don't SPAM here.