How to get the max value for each group in Oracle?

Question

I've found some solutions for this problem, however, they don't seem to work with Oracle.

I got this:

I want a view to present only the informations about the oldest person for each team. So, my output should be something like this:

PERSON  | TEAM | AGE
Sam     | 1    | 23
Michael | 2    | 21

How can I do that in Oracle?

Answer 1

select * from table
where (team, age) in (select team, max(age) from table group by team)

Answer 2

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, age
from t0
where rn = 1;

Answer 3

One method uses keep:

select team, max(age) as age,
       max(person) keep (dense_rank first order by age desc) as person
from t
group by team;

There are other methods, but in my experience, keep works quite well.

Answer 4

select * from (select person,team,age,
       dense_rank() over (partition by team order by age desc) rnk)
       where rnk=1;