How to Get the Top N Results per Group in Oracle Without Subqueries?

Get top results for each group in Oracle database

Question:

Get the top N results for each group in an Oracle database without using a subquery.

Example:

Consider the given table containing employee information:

emp_id	name	occupation
1	John Smith	Accountant
2	Jane Doe	Engineer
3	Jack Black	Funnyman

The goal is to retrieve three employees (top 3) from each occupation without using a subquery.

Solution:

<code class="language-sql">select *
from (select emp_id, name, occupation,
      rank() over (partition by occupation order by emp_id) as rank
      from employee)
where rank <= 3;</code>

Instructions:

The

rank() function is used to assign a ranking to each employee within their respective occupation. The partition by occupation clause ensures that each occupation is ranked separately.

rank <= 3 The condition filters out employees whose ranking is less than or equal to 3 in each occupation.

Additional information:

For more information about the rank() function, please see the following resources: https://www.php.cn/link/56138186020206c2b1d99be41c8dc9b8

The above is the detailed content of How to Get the Top N Results per Group in Oracle Without Subqueries?. For more information, please follow other related articles on the PHP Chinese website!