How to get the max value for each group in Oracle?
select * from tablewhere (team, age) in (select team, max(age) from table group by team)
One method uses keep:
select team, max(age) as age, max(person) keep (dense_rank first order by age desc) as personfrom tgroup by team;There are other methods, but in my experience, keep works quite well.
Here is an example without keep but with row_number():
with t0 as( select person, team, age, row_number() over(partition by team order by age desc) as rn from t)select person, team, agefrom t0where rn = 1;