按列排序

Question

我有一个如下表。

201610 4435230309000131 5969 
201601 4435230309000149 5969 
201702 4435230309000149 5977 
201702 4435230309000156 5962 
201612 4435230309000156 7399 
201702 4435230309000164 4812 
201702 4435230309000172 7230 
201702 4435230309000180 5968 
201701 4435230309000180 5977 

我想编写一个查询来返回对应于最近日期（第一列）的第二列和第三列。更具体地说，我不想在结果表中看到以下记录。

201702 4435230309000156 5962 
201612 4435230309000156 7399 

虽然，我要的是检索

201702 4435230309000156 5962 

它与最近的日期（201702）相关

。

第一列是“int”格式。

您的帮助表示赞赏。

感谢，

Answer 1

如果你只是想最近的行，那么你可以使用top 1像这样：

select top 1 * 
from t 
order by date desc 

如果您想获得最新的行为每个col2可以在像这样的公共表表达式的子查询使用row_number()：

select 
    [date] 
    , col2 
    , col3 
from (
    select * 
    , rn = row_number() over (
     partition by col2 
     order by [date] desc 
     ) 
    from t 
) as s 
where rn = 1 

Answer 2

你想检索数据如下逻辑是什么？

DECLARE @tb TABLE(col1 INT,col2 VARCHAR(20),col3 INT) 
INSERT INTO @tb 
SELECT 201610,'4435230309000131',5969 UNION ALL 
SELECT 201601,'4435230309000149',5969 UNION ALL 
SELECT 201702,'4435230309000149',5977 UNION ALL 
SELECT 201702,'4435230309000156',5962 UNION ALL 
SELECT 201612,'4435230309000156',7399 UNION ALL 
SELECT 201702,'4435230309000164',4812 UNION ALL 
SELECT 201702,'4435230309000172',7230 UNION ALL 
SELECT 201702,'4435230309000180',5968 UNION ALL 
SELECT 201701,'4435230309000180',5977 
SELECT * FROM (
SELECT MAX(col1)OVER() AS RecentDay, *,ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col2) AS rn1, ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col3) AS rn2 FROM @tb 
) AS t WHERE t.RecentDay=t.col1 AND t.rn1=t.rn2 

 
RecentDay col1  col2     col3  rn1     rn2 
----------- ----------- -------------------- ----------- -------------------- -------------------- 
201702  201702  4435230309000156  5962  2     2