OracleSQL Finding top N rows from a group

Question

I am trying Query the top N of rows but with a group inside of it inside of OracleSQL

For example:

+-----+----------+----+------+
|JobID|JobTitle  |Pay |Name  |
+-----+----------+----+------+
|012  |Manager   |400 |Bob   |
|012  |Manager   |400 |Gemma |
|012  |Manager   |400 |Ash   |
|020  |Supervisor|400 |Dan   |
|020  |Supervisor|400 |Hannah|
|013  |Clerk     |300 |Sarah |
+-----+----------+----+------+

As from the example above I would like to find all of the employees under a job title and then only show the top 3 most paid jobs and then ordering by pay down.

+-----+----------+----+------+
|JobID|JobTitle  |Pay |Name  |
+-----+----------+----+------+
|02   |Manager   |400 |Bob   |
|02   |Manager   |400 |Gemma |
|02   |Manager   |400 |Ash   |
|04   |Supervisor|400 |Dan   |
|04   |Supervisor|400 |Hannah|
|03   |Clerk     |300 |Sarah |
|01   |Cleaner   |200 |Scruff|
|01   |Cleaner   |200 |Fry   |
+-----+----------+----+------+

Would be the table.

I have tried using a subquery and then adding on rownum <= 3; on the end but still haven't managed to find the desired result.

    SELECT * FROM 
      ( SELECT JobID, MAX(Pay) AS Pay, JobTitle, EmpID, Name 
        FROM Employees,Jobs
        WHERE JobID = Employees.Job_ID
        GROUP BY JobID, Pay, EmpID, JobTitle,
        ORDER BY Pay DESC)
     WHERE ROWNUM <= 3;

score 2 · Accepted Answer · edited Mar 30 '15 at 15:26

2

This is best done using a window function:

select jobid, jobtitle, pay, name
from (
  select j.jobid, j.jobtitle, e.pay, e.name, 
         row_number() over (partition by j.jobid order by e.pay desc) as rn
  from employees e 
    join jobs j on j.jobid = e.job_id
) 
where rn <= 3
order by jobid, jobtitle, pay desc;

I also changed the outdated implicit joins in the where clause into an explicit JOIN

edited Mar 30 '15 at 15:26

Dmitriy

5,525
12
25
38

answered Mar 30 '15 at 14:33

I am getting ERROR at line 9: ORA-00933: SQL command not properly ended – Lero Mar 30 '15 at 15:12
it's because the `WHERE` is below the `ORDER BY` - just transpose the two and watch the semicolon. – David Faber Mar 30 '15 at 15:16
That worked but for some reason I'm still pulling all my data instead of just the first 3, also when I order by jobid, jobtitle, pay I get the results but in the oposite order. – Lero Mar 30 '15 at 15:57

OracleSQL Finding top N rows from a group

1 Answers1