SELECT most recent record per item

[sjarman]

Hi,

This is probably easy but I'm having trouble getting it done. The following is an example table similar to mine:

ID DATETIME READING
1 10/10/07 50
2 10/10/07 65
3 10/10/07 12
2 11/10/07 43
1 11/10/07 89

What I need to do is select the most recent reading for each ID, so I end up with this:

ID DATETIME READING
1 11/10/07 89
2 11/10/07 43
3 10/10/07 12

Can someone please help me with the SELECT statement? I've been playing with TOP and DISTINCT and stuff, but they don't seem like the right choices.

 

[gmmastros]

Select Table.Id, Table.DateTime, Table.Reading
From   Table
       Inner Join (
          Select Id, Max(DateTime) As MaxDateTime
          From   Table
          Group By Id
          ) As A
          On  Table.Id = A.Id
          And Table.DateTime = A.DateTime

This may bring back extra rows if there are duplicate Id/DateTime combinations.

-George

"the screen with the little boxes in the window." - Moron

 

[PHV]

I'd replace this:
And Table.DateTime = A.DateTime
with this:
And Table.DateTime = A.MaxDateTime

 

[sjarman]

Brilliant! And thanks for the immediate reply

All up and running now...

 

[sjarman]

Yep thanks PHV - figured out the typo. All working!