table:test
ID NO name time
A | 1 | aa | 2011-11-23 5:00:00 |
A | 2 | bb | 2011-11-23 1:00:00 |
A | a3 | cc | 2011-11-23 2:00:00 |
B | 1 | aaa | 2011-11-23 0:00:00 |
B | c2 | bbb | 2011-11-23 2:00:00 |
B | 3 | ccc | 2011-11-23 4:00:00 |
C | 1 | aaaa | 2011-11-23 0:00:00 |
select *
from test a
where not exists (select top(1) * from test b
where b.id=a.id and b.time>a.time)
A 1 aa 2011-11-23 05:00:00.000
B 3 ccc 2011-11-23 04:00:00.000
C 1 aaaa 2011-11-23 00:00:00.000