Get top results for each group (in Oracle)

Question

How would I be able to get N results for several groups in an oracle query.

For example, given the following table:

|--------+------------+------------|
| emp_id | name       | occupation |
|--------+------------+------------|
|      1 | John Smith | Accountant |
|      2 | Jane Doe   | Engineer   |
|      3 | Jack Black | Funnyman   |
|--------+------------+------------|

There are many more rows with more occupations. I would like to get three employees (lets say) from each occupation.

Is there a way to do this without using a subquery?

This is **NOT** a duplicate of [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) - that question is looking for a single-row-per-group and the majority of the solutions are not applicable to this question which is asking for multiple-rows-per-group. — MT0, Sep 26 '18 at 09:14

score 42 · Answer 1 · edited Nov 29 '10 at 20:11

42

I don't have an oracle instance handy right now so I have not tested this:

select *
from (select emp_id, name, occupation,
      rank() over ( partition by occupation order by emp_id) rank
      from employee)
where rank <= 3

Here is a link on how rank works: http://www.psoug.org/reference/rank.html

edited Nov 29 '10 at 20:11

John Siracusa

14,971
7
42
54

answered Sep 25 '08 at 18:27

jop

82,837
10
55
52

2

Didnt he specify without a subquery...? – AviD Sep 25 '08 at 19:26
2

Yes, but he may well have meant "without using a subquery that selects from the same table again". This solution uses a subquery but only accesses the table once. – Tony Andrews Sep 26 '08 at 10:32
Works great, seems to be lean on the DB as well. – cody.tv.weber Mar 20 '18 at 17:58

Bill Karwin · Accepted Answer · 2018-09-26T19:48:39.453

13

This produces what you want, and it uses no vendor-specific SQL features like TOP N or RANK().

SELECT MAX(e.name) AS name, MAX(e.occupation) AS occupation 
FROM emp e 
  LEFT OUTER JOIN emp e2 
    ON (e.occupation = e2.occupation AND e.emp_id <= e2.emp_id) 
GROUP BY e.emp_id 
HAVING COUNT(*) <= 3 
ORDER BY occupation;

In this example it gives the three employees with the lowest emp_id values per occupation. You can change the attribute used in the inequality comparison, to make it give the top employees by name, or whatever.

edited Sep 26 '18 at 19:48

answered Sep 25 '08 at 20:48

Bill Karwin

538,548
86
673
828

@codemon2002, use the answer posted by jop on this thread. In Oracle, you can use windowing functions, which are intended for this kind of query. – Bill Karwin Mar 20 '18 at 17:50

score 3 · Answer 3 · edited Jun 07 '11 at 10:19

3

Add RowNum to rank :

select * from 
         (select emp_id, name, occupation,rank() over ( partition by occupation order by emp_id,RowNum) rank   
                      from employee) 
         where rank <= 3

edited Jun 07 '11 at 10:19

Andrei Sfat

8,440
5
49
69

answered Jun 07 '11 at 10:05

trung

31
1

score 1 · Answer 4 · edited Jul 02 '14 at 13:47

I'm not sure this is very efficient, but maybe a starting place?

select *
from people p1
    join people p2
        on p1.occupation = p2.occupation
    join people p3
        on p1.occupation = p3.occupation
        and p2.occupation = p3.occupation
where p1.emp_id != p2.emp_id
    and p1.emp_id != p3.emp_id

This should give you rows that contain 3 distinct employees all in the same occupation. Unfortunately, it will give you ALL combinations of those.

Can anyone pare this down please?

score 1 · Answer 5 · edited Sep 26 '12 at 19:00

1

tested this in SQL Server (and it uses subquery)

select emp_id, name, occupation
from employees t1
where emp_id IN (select top 3 emp_id from employees t2 where t2.occupation = t1.occupation)

just do an ORDER by in the subquery to suit your needs

edited Sep 26 '12 at 19:00

dugas

12,025
3
45
51

answered Sep 25 '08 at 18:32

Leon Tayson

4,741
7
37
36

Get top results for each group (in Oracle)

5 Answers5

Linked

Related