Link to home
Start Free TrialLog in
Avatar of scooper082898
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
ASKER CERTIFIED SOLUTION
Avatar of Brendt Hess
Brendt Hess
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of scooper082898
scooper082898

ASKER

Mch appreciated. I have never used "Filter on" before.