scooper082898
asked on
Duplicate records - Select based on latest date
Given the following table:
ID ItemNo Datestamp
1 10 2009-03-27 15:58:18.000
2 10 2009-03-27 16:07:14.000
3 20 2009-03-27 16:17:15.000
4 30 2009-03-27 16:42:17.000
5 30 2009-03-27 16:33:33.000
6 40 2009-03-27 16:21:46.000
7 50 2009-03-27 16:49:18.000
Notice that the ID is unique (actually in my table it is a guid) and the ItemNo field has some duplicates.
I would like to query all the fields in this table and select only the records with the latest datestamp - if there are multiple records with the same item number, get only 1 record for that ItemNo and choose the latest datestamp. How would I do that?
I have tried a few different queries using Group By, but have not been completely successful.
Thanks in advance,
Steve
ID ItemNo Datestamp
1 10 2009-03-27 15:58:18.000
2 10 2009-03-27 16:07:14.000
3 20 2009-03-27 16:17:15.000
4 30 2009-03-27 16:42:17.000
5 30 2009-03-27 16:33:33.000
6 40 2009-03-27 16:21:46.000
7 50 2009-03-27 16:49:18.000
Notice that the ID is unique (actually in my table it is a guid) and the ItemNo field has some duplicates.
I would like to query all the fields in this table and select only the records with the latest datestamp - if there are multiple records with the same item number, get only 1 record for that ItemNo and choose the latest datestamp. How would I do that?
I have tried a few different queries using Group By, but have not been completely successful.
Thanks in advance,
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER