按列和最大日期分组时如何选择单行? [英] How to select a single row when grouping by column and by max date?

问题描述

我要过滤以下数据，因此仅根据第一列的分组仅获得一行，然后选择最大日期

I have the following data which I would like to filter so I only get only one row based on the grouping of the first column and select the max date

二氧化碳包含唯一值

col1 | col2 | date

 1   |  123 | 2013
 1   |  124 | 2012
 1   |  125 | 2014
 2   |  213 | 2011
 2   |  214 | 2015
 2   |  215 | 2018

所以我想要的结果是:

 1   |  125 | 2014
 2   |  215 | 2018

我尝试使用一些我在这里(如下所示)找到的示例，以及其他按/distinct/max(date)分组但没有运气的示例

I've tried using a few examples which I found on here (as below) as well other group by / distinct / max(date) but with no luck

select t.*
from (select t.*,
             row_number() over (partition by col1, col2 order by date desc) as seqnum
      from t
     ) t
where seqnum = 1

推荐答案

将row_number()中的分区更改为仅按col1进行分区，但按date desc保留顺序:

Change the partition in the row_number() to only partition by col1 but keep the order by date desc:

select col1, col2, date
from 
(
  select col1, col2, date,
    row_number() over (partition by col1 
                       order by date desc) as rn
  from yourtable
) x
where rn = 1

请参见带演示的SQL提琴.

由于您同时按col1和col2进行了分区，因此每一行的值都是唯一的.因此它不会返回具有最大日期的行.

Since you were partitioning by both col1 and col2 you were getting unique values for each row. So it would not return the row with the max date.

这篇关于按列和最大日期分组时如何选择单行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！