SQL Select First column and for each row select unique ID and the last date

Question

I have a problems this mornig , I have tried many solutions and nothing gave me the expected result.

I have a table that looks like this :

+----+----------+-------+
| ID | COL2     | DATE  | 
+----+----------+-------+
|  1 | 1        |  2001 | 
|  1 | 2        |  2002 | 
|  1 | 3        |  2003 | 
|  1 | 4        |  2004 | 
|  2 | 1        |  2001 | 
|  2 | 2        |  2002 | 
|  2 | 3        |  2003 | 
|  2 | 4        |  2004 | 
+----+----------+-------+

And I have a query that returns a result like this : I have the unique ID and for this ID I want to take the last date of the ID

+----+----------+-------+
| ID | COL2     | DATE  | 
+----+----------+-------+
|  1 | 4        |  2004 | 
|  2 | 4        |  2004 | 
+----+----------+-------+

But I don't have any idea how I can do that. I tried Join , CROSS APPLY ..

If you have some idea ,

Thank you

Clement FAYARD

Answer 1

declare @t table (ID INT,Col2 INT,Date INT)
insert into @t(ID,Col2,Date)values (1,1,2001)
insert into @t(ID,Col2,Date)values (1,2,2001)
insert into @t(ID,Col2,Date)values (1,3,2001)
insert into @t(ID,Col2,Date)values (1,4,2001)
insert into @t(ID,Col2,Date)values (2,1,2002)
insert into @t(ID,Col2,Date)values (2,2,2002)
insert into @t(ID,Col2,Date)values (2,3,2002)
insert into @t(ID,Col2,Date)values (2,4,2002)

;with cte as(
    select
        *,
        rn = row_number() over(partition by ID order by Col2 desc)
    from @t

)
select
    ID,
    Col2,
    Date
from cte
where
    rn = 1