How to Retrieve the Top N Highest Values in Oracle SQL?

Extracting Top N Highest Values in Oracle SQL

Challenge: How to efficiently select a specific number of rows containing the highest values from a particular column within an Oracle SQL query?

Solution:

The optimal approach utilizes Oracle's analytic functions, specifically RANK() or DENSE_RANK():

Employing the RANK() Function:

<code class="language-sql">select * from (
  select empno, sal, rank() over (order by sal desc) as rnk
  from emp
)
where rnk <= 5;</code>

This query retrieves the top 5 highest salaries, along with the corresponding employee numbers.

Utilizing the DENSE_RANK() Function:

<code class="language-sql">select * from (
  select empno, sal, dense_rank() over (order by sal desc) as rnk
  from emp
)
where rnk <= 5;</code>

DENSE_RANK() offers an advantage over RANK() by eliminating gaps in the ranking when multiple rows share the same value.

Important Considerations:

• While the ROW_NUMBER() analytic function can also achieve this, RANK() or DENSE_RANK() are generally preferred for selecting top N values because they handle ties more gracefully.
• Avoid using the ROWNUM pseudocolumn as a standalone solution. Its application before the ORDER BY clause can lead to inaccurate results.

The above is the detailed content of How to Retrieve the Top N Highest Values in Oracle SQL?. For more information, please follow other related articles on the PHP Chinese website!