时间戳

首页 > 解决方案 > 从每个组中获取最大版本

sql - 从每个组中获取最大版本

问题描述

我需要获取 Oralce SQL 中每个版本的最大记录

Price  Total_Amount  Group   Version 
10      100           1       20190401.00
11      111           1       20190501.00 --- Print this version 
5       50            2       20190401.00 --- Print this version
6       60            3       20190401.00
7       70            3       20190501.00
8       80            3       20190601.00
9       90            3       20190701.00  --- Print this version

上面的查询是从

select
    sum(price),
    sum(Total_amount),
    group,
    version,
    row_number() over(Partition by group order by version) row_num,
    dense_Rank over ( order by version) dense_Rank
from Table_name 
group by group,version

我尝试在上面的查询中对组和密集等级使用分区，它给了我每个组中的最大值，但是当我尝试从上面查询结果时，它没有给我想要的结果

期望的输出

Price  Total_Amount  Group   Version
11      111           1       20190501.00 
5       50            2       20190401.00
9       90            3       20190701.00

标签： sqloraclegreatest-n-per-group

解决方案

一种方法使用lead()：

select t.*
from (select t.*,
             lead(version) over (partition by group order by version) as next_version
      from t
     ) t
group next_version is null;

在 Oracle 中，您还可以使用聚合来表达这一点，keep语法如下：

select group, max(version),
       max(price) keep (dense_rank first order by version desc) as price,
       max(total_amount) keep (dense_rank first order by version desc) as total_amount
from t
group by group;

推荐阅读