SQL选择第一列，并为每一行选择唯一的ID和最后日期 [英] SQL Select First column and for each row select unique ID and the last date

问题描述

我在早上遇到问题，我尝试了许多解决方案，但没有任何结果给我带来预期的结果.

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

我有一个像这样的表:

+----+----------+-------+
| 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 | 
+----+----------+-------+

我有一个查询，返回的结果是这样的: 我有唯一的ID，对于该ID，我想获取ID的最后日期

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 ..

如果您有想法，

谢谢

克莱门特·法亚德

推荐答案

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

这篇关于SQL选择第一列，并为每一行选择唯一的ID和最后日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！