<p>How would I be able to get N results for several groups in
an oracle query.</p>

<p>For example, given the following table:</p>

<pre><code>|--------+------------+------------|
| emp_id | name       | occupation |
|--------+------------+------------|
|      1 | John Smith | Accountant |
|      2 | Jane Doe   | Engineer   |
|      3 | Jack Black | Funnyman   |
|--------+------------+------------|
</code></pre>

<p>There are many more rows with more occupations.  I would like to get
three employees (lets say) from each occupation.</p>

<p>Is there a way to do this without using a subquery?</p>

I'm not sure this is very efficient, but maybe a starting place?
```
```
select *
from people p1
    join people p2
        on p1.occupation = p2.occupation
    join people p3
        on p1.occupation = p3.occupation
        and p2.occupation = p3.occupation
where p1.emp_id != p2.emp_id
    and p1.emp_id != p3.emp_id
```
```
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?