| Author |
Topic |
|
rkruis
Starting Member
28 Posts |
 Posted - 2011-05-09 : 12:50:58
|
How can I select using distinct with a second column being date and only return the most recent record for each group.My table consist of four fields. ID, ID_GROUP, DATE, OTHER. I would like only one ID_GROUP record, and that record to be the most recent DATE.Records look like this:1, 20, 'July 1, 2010', ...2, 20, 'July 2, 2010', ...3, 20, 'July 4, 2010', ...4, 20, 'July 6, 2010', ...5, 21, 'July 1, 2010', ...6, 21, 'July 2, 2010', ...7, 21, 'July 4, 2010', ...8, 21, 'July 6, 2010', ...I would like to get back:1, 20, 'July 1, 2010', ...5, 21, 'July 2, 2010', ...Select ID, DISTINCT(ID_GROUP), DATEfrom TABLEorder by DATE desc |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-09 : 12:59:29
|
| I'm guessing that 1,2,3 etc. represent the ID field, 20 and 21 the ID_Group field. What is the Data type for the DATE field? Also, do you oldest and not most recent? The most recent record for ID_Group 20 is July 6, 2010.JimEveryday I learn something that somebody else already knew |
 |
|
|
rkruis
Starting Member
28 Posts |
Posted - 2011-05-09 : 13:04:08
|
| Hi Jim,Yes, 1,2,3 is the ID field and 20, 21 are the group field. And the date is a datetime. However, sorting is not the issue.The issue I am having is, how do I get just the top record (DATE most recent) for each group back. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-09 : 13:21:39
|
| This gives you what you asked for, but it is not what you showed in your expected output. If you want the expected output change the desc to asc in the over() clause.. JimSELECT *FROM(Select ID, ID_GROUP , DATE ,row_number() over(partition by ID_Group order by date desc)from TABLEorder by DATE desc) tWHERE row = 1ORDER BY DATE DESCEveryday I learn something that somebody else already knew |
|
|
|
rkruis
Starting Member
28 Posts |
Posted - 2011-05-09 : 14:02:53
|
| Thanks Jim. That did the trick |
|
|
|
|
|
|
Distinct with order by DATE