10

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

I got this:

enter image description here

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?

user7243231
  • 111
  • 1
  • 1
  • 3

5 Answers5

8

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;
Nikita Kotlyarov
  • 81
  • 1
7
select * from table
where (team, age) in (select team, max(age) from table group by team)
arturro
  • 1,598
  • 1
  • 10
  • 13
5

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This method works, however is there any simpler method? Teachers never taught us "keep" – user7243231 Dec 02 '16 at 21:53
  • I would consider this as being the simplest method from all answers you see here. However [FIRST](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm) is not that commonly known. – Wernfried Domscheit Dec 03 '16 at 06:51
  • Good, but when I add more rows it expands. How to keep other columns for these rows I've queried? – Peter.k Jan 04 '19 at 05:26
  • @Peter.k . . . I would suggest that you ask a *new* question with appropriate sample data and desired results and explanation of what you want to do. – Gordon Linoff Jan 04 '19 at 12:36
1

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

Using an Analytic Function returns all people with maximum age per team (needed if there are people with identical ages), selects Table one time only and is thus faster than other solutions that reference Table multiple times:

With MaxAge as (
  Select T.*, Max (Age) Over (Partition by Team) MaxAge
  From Table T
)
Select Person, Team, Age From MaxAge Where Age=MaxAge
;

This also works in MySQL/MariaDB.

Juergen
  • 55
  • 1
  • 5