日期:2014-05-17 浏览次数:20427 次
SELECT MAX(ID) ID,
COL2,
COL3,
COL4
FROM [TABLE]
GROUP BY COL2,COL3,COL4
select *
from tb a
where a.id=(select max(id) from tb b where a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3)
;WITH c1(ID, COL2, COL3, COL4)
AS
(
SELECT 1, 'A', 'A', 'A' union all
SELECT 2, 'A', 'A', 'A' union all
SELECT 2, 'B', 'B', 'B' union all
SELECT 3, 'B', 'B', 'B' union all
SELECT 3, 'C', 'C', 'C' union all
SELECT 5, 'C', 'C', 'C'
)
--start query
SELECT
(SELECT MAX(ID) FROM c1 WHERE COL2 = d.COL2 AND COL3 = d.COL3 AND COL4 = d.COL4) id
,COL2
,COL3
,COL4
FROM c1 d
GROUP BY COL2, COL3, COL4
--result
id COL2 COL3 COL4
----------- ---- ---- ----
2 A A A