I have a table
PKID : int - primary key
Date 开发者_开发知识库: datetime
ID_2 : int
lots of other columns..
...
...
How do I get the line with the highest Date for each unique ID_2 ?
;WITH cte AS
(
SELECT PKID,
Date,
ID_2,
ROW_NUMBER() OVER (PARTITION BY ID_2 ORDER BY Date DESC) AS RN
FROM your_table
)
SELECT PKID,
Date,
ID_2
FROM cte
WHERE RN=1
;with t as
(
select
ROW_NUMBER() OVER (PARTITION BY ID_2 order by date desc) rn,tableName.*
from
tableName
)
select * from t where rn=1
I'm lazy and used *. You shouldn't
Simply MAX()
and GROUP
;
SELECT
ID_2,
MAX([Date]) AS TheDate
FROM tbl
GROUP BY ID_2
精彩评论