getting a line which has the highest date for each id sql server 2005

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