获取具有组最大值的每一行

Question

我得到了下表：

col1 | col2 | col3 | col4 | timestamp
-------------------------------------
1  | ...  | ...  | ...  | 12:01
1  | ...  | ...  | ...  | 12:40
2  | ...  | ...  | ...  | 11:00
2  | ...  | ...  | ...  | 13:00
2  | ...  | ...  | ...  | 12:22
3  | ...  | ...  | ...  | 16:00
3  | ...  | ...  | ...  | 12:10

我想获取按第 1 列分组的最大时间戳值的每一行。 这意味着结果必须如下：

col1 | col2 | col3 | col4 | timestamp
-------------------------------------
1  | ...  | ...  | ...  | 12:40
2  | ...  | ...  | ...  | 13:00
3  | ...  | ...  | ...  | 16:00

我的以下查询有效：

SELECT col1, MAX(timestamp)
FROM table
GROUP BY col1

但不是这个：

SELECT col1, col2, col3, col4, MAX(timestamp)
FROM table
GROUP BY col1

Answer 1

根据col1列的分区和timestamp列的降序给出row number。并选择具有rn = 1的行。

查询

;with cte as(
select [rn] = row_number() over(
partition by [col1]
order by [timestamp] desc
), *
from your_table_name
)
select [col1], [col2], [col3], [col4], [timestamp]
from cte
where [rn] = 1;

Answer 2

好吧，您可以按照它适合您的方式获取最大时间戳，然后与初始表联接以获取其他值，如下所示：

select t.col1, t.col2, t.col3, t.col4, tmax.max_ts
from table t join   (
select col1, max(timestamp) max_ts
from table
group by col1
) tmax on tmax.col1 = t.col1 and tmax.max_ts = t.timestamp