比如例子是这样
ID DATE STATE
2 2009-09-17 1
2 2009-09-19 2
2 2009-09-07 3
1 2009-09-01 2
1 2009-09-27 1
我想按时间选出ID最新状态,结果为 2 2009-09-19 2
1 2009-09-27 1该怎么构造语句呢
ID DATE STATE
2 2009-09-17 1
2 2009-09-19 2
2 2009-09-07 3
1 2009-09-01 2
1 2009-09-27 1
我想按时间选出ID最新状态,结果为 2 2009-09-19 2
1 2009-09-27 1该怎么构造语句呢
from tb a
where not exists (select 1 from tb b where a.id=b.id and a.id<b.id)
(select id,max([date]) as maxdate from t) b
on a.id=b.id and a.[date]=b.maxdate
TABLE 名为 SVR
ID DATE STATE
2 2009-09-17 1
2 2009-09-19 2
2 2009-09-07 3
1 2009-09-01 2
1 2009-09-27 1
想得到的结果是
2 2009-09-19 2
1 2009-09-27 1
就是按时间查出每个ID的最新状态分不够的话继续加
SELECT 2,'2009-09-17',1 UNION ALL
SELECT 2,'2009-09-19',2 UNION ALL
SELECT 2,'2009-09-07',3 UNION ALL
SELECT 1,'2009-09-01',2 UNION ALL
SELECT 1,'2009-09-27',1
SELECT * FROM #DBTemp T WHERE DATE=(SELECT MAX(DATE) FROM #DBTemp WHERE ID=T.ID)DROP TABLE #DBTemp