我想使用PL / SQL(Oracle)获得第二高的最大值。
我有一个看起来像这样的表:
CLIENT | ORDER_DATE
1 | 14/09/2018
1 | 01/02/2019
2 | 13/12/2019
2 | 01/01/2020
2 | 15/12/2019
我想为每个客户端获取具有max(ORDER_DATE)和第二高的max(ORDER_DATE)的表:
CLIENT | MAX(ORDER_DATE) | 2nd highest max(ORDER_DATE)
1 | 01/02/2019 | 14/09/2018
2 | 01/01/2020 | 15/12/2019
我尝试使用等级,但只有一行(一个随机客户端):
select *
from (select CLIENT,
max(ORDER_DATE),
row_number() over (order by max(ORDER_DATE) desc) as rk
from order_table
group by CLIENT) t
where rk = 2
在应用诸如以下的分析函数后,您需要条件聚合ROW_NUMBER()
:
WITH t2 AS
(
SELECT client,order_date,
ROW_NUMBER() OVER (PARTITION BY client ORDER BY order_date DESC) as rk
FROM order_table
)
SELECT client,
MAX(CASE WHEN rk=1 THEN order_date END) AS "max order date",
MAX(CASE WHEN rk=2 THEN order_date END) AS "2nd highest max ord.date"
FROM t2
GROUP BY client
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句