Get top results for each group (in Oracle)

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?

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

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.