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 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.