Menu
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?
565
Use this command to display a specified number of results with the highest aggregated value as determined by the specified field.
First, you need to write a CTE in which you assign a number to each row within each group. To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ).
Generally, here is how to select only the first row for each unique value of a column: Navigate to the Data tab and tap on Advanced under Sort & Filter. In the pop-up catalog, include the list range as the first column and check Unique Records Only. Click OK to select the first row with unique values.
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
166
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
