取分组 日期最大的数据解决方法

取分组 日期最大的数据
将数据 分组，取得日期最大的记录，如果日期最大有相同 就一起取
如下：[code=SQL][/code]
IDDate
12012-3-10
12012-3-10
12012-3-4
22012-2-10
22012-2-10
22012-3-8
22012-3-9
32012-5-12
32012-4-12
32012-1-2
42012-5-5
52012-3-7
62012-4-2
72012-2-1
要 获得这样的结果：

IDDate
12012-3-10
12012-3-10
22012-3-9
32012-5-12
42012-5-5
52012-3-7
62012-4-2
72012-2-1


[解决办法]
SELECT * FROM TB T WHERE DATE=(SELECT MAX(DATE) FROM TB WHERE ID=T.ID)
[解决办法]
1楼的结果不对

SQL code

WITH test (ID,    [Date]) AS  ( SELECT 1,    '2012-3-10' UNION ALL SELECT 1,    '2012-3-10' UNION ALL SELECT 1,    '2012-3-4' UNION ALL SELECT 2,    '2012-2-10' UNION ALL SELECT 2,    '2012-2-10' UNION ALL SELECT 2,    '2012-3-8' UNION ALL SELECT 2,    '2012-3-9' UNION ALL SELECT 3,    '2012-5-12' UNION ALL SELECT 3,    '2012-4-12' UNION ALL SELECT 3,    '2012-1-2' UNION ALL SELECT 4,    '2012-5-5' UNION ALL SELECT 5,    '2012-3-7' UNION ALL SELECT 6,    '2012-4-2' UNION ALL SELECT 7,    '2012-2-1' ) SELECT id,MIN ([Date])[Date] FROM Test  GROUP BY id  /* id          Date ----------- --------- 1           2012-3-10 2           2012-2-10 3           2012-1-2 4           2012-5-5 5           2012-3-7 6           2012-4-2 7           2012-2-1  (7 行受影响) */
[解决办法]
探讨

1楼的结果不对

SQL code

WITH test (ID,    [Date])
 AS 
 (
 SELECT 1,    '2012-3-10'
 UNION ALL SELECT 1,    '2012-3-10'
 UNION ALL SELECT 1,    '2012-3-4'
 UNION ALL SELECT 2,    '2012-2-10'
 UN……

[解决办法]
探讨

原来倒序了.....我说怎么执行你的和楼主结果不一样呢