Most current date

[simian101]

What is the best way to get the most current dates from a list for each id?

ID Date
1044 2009-07-31 14:17:43.000
1044 2009-07-31 14:15:44.000
1044 2009-07-31 14:11:44.000
1047 2009-07-31 14:33:19.000
1047 2009-08-11 16:58:41.000
1047 2009-08-10 11:51:01.000
1051 2009-08-10 12:51:01.000
1051 2009-08-10 11:31:01.000
1051 2009-08-10 11:11:01.000

I would want it to produce.

ID Date
1044 2009-07-31 14:17:43.000
1047 2009-08-11 16:58:41.000
1051 2009-08-10 12:51:01.000

Thanks

Simi

 

[gmmastros]

Select Id, Max(Date) 
From   YourTable
Group By Id

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian101]

Thanks George.

Simi

 

[simian101]

I simplified my example to much...

There is also a record number that I need so I can get back to record for more fields.

RecNo ID Date
1001 1044 2009-07-31 14:17:43.000
1002 1044 2009-07-31 14:15:44.000
1003 1044 2009-07-31 14:11:44.000
1004 1047 2009-07-31 14:33:19.000
1005 1047 2009-08-11 16:58:41.000
1006 1047 2009-08-10 11:51:01.000
1007 1051 2009-08-10 12:51:01.000
1008 1051 2009-08-10 11:31:01.000
1009 1051 2009-08-10 11:11:01.000

Is there a way to also get that recno?

ID Date Recno
1044 2009-07-31 14:17:43.000 1001
1047 2009-08-11 16:58:41.000 1005
1051 2009-08-10 12:51:01.000 1007

I can't link back to the date becuase it is not unique.

Thanks again

Simi

 

[bborissov]

SELECT YourTable.*
FROM YourTable
INNER JOIN (SELECT Id, Max(Date) AS Date
                   FROM YourTable
            GROUP BY Id) Tbl1
ON YourTable.Id   = Tbl1.Id AND
   YourTable.Date = Tbl1.Date

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

See Including an Aggregated Column's Related Values

 

[simian101]

Thanks Borislav, that seems to do the trick.

Excellent link Markros, I will continue to study that.

Thanks

Simi

 

[markros]

If you would have time to study that link, you may also want to review a similar problem's thread

http://forums.asp.net/p/1462968/3372551.aspx#3372551

See test case scenarios and test results at the very end of it. Nikola's suggestion of TOP 1 seemed to outperform other cases.