Get top results for each group (in Oracle)
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 <= 3Here is a link on how rank works: http://www.psoug.org/reference/rank.html
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.
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