row_number()
相关的示例,但没有keep
的实现:
with t0 as
(
select person, team, age,
row_number() over(partition by team order by age desc) as rn
from t
)
select person, team, age
from t0
where rn = 1;
select * from table
where (team, age) in (select team, max(age) from table group by team)
其中一种方法是使用keep
:
select team, max(age) as age,
max(person) keep (dense_rank first order by age desc) as person
from t
group by team;
还有其他方法,但根据我的经验,keep
方法非常有效。
With MaxAge as (
Select T.*, Max (Age) Over (Partition by Team) MaxAge
From Table T
)
Select Person, Team, Age From MaxAge Where Age=MaxAge
;
这也适用于MySQL/MariaDB。
select * from (select person,team,age,
dense_rank() over (partition by team order by age desc) rnk)
where rnk=1;