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?
Add RowNum to rank :
tested this in SQL Server (and it uses subquery)
just do an ORDER by in the subquery to suit your needs
This produces what you want, and it uses no vendor-specific SQL features like TOP N or RANK().
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.
I'm not sure this is very efficient, but maybe a starting place?
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?
I don't have an oracle instance handy right now so I have not tested this:
Here is a link on how rank works: http://www.psoug.org/reference/rank.html