How Can I Efficiently Retrieve the Top N Highest Values in Oracle SQL?

Extracting Top N Records in Oracle SQL

Need to fetch a specific number of rows with the highest or lowest values in a column? Oracle's analytic functions, like RANK() and DENSE_RANK(), provide efficient solutions.

For example, to identify the top five highest-earning employees, use RANK():

<code class="language-sql">SELECT * FROM (
  SELECT empno, sal, RANK() OVER (ORDER BY sal DESC) AS rnk
  FROM emp
)
WHERE rnk <= 5;</code>

DENSE_RANK() offers a variation, eliminating gaps in ranking caused by ties:

<code class="language-sql">SELECT * FROM (
  SELECT empno, sal, DENSE_RANK() OVER (ORDER BY sal DESC) AS rnk
  FROM emp
)
WHERE rnk <= 5;</code>

The best approach depends on your specific needs.

ROW_NUMBER() assigns a unique rank to each row, but it arbitrarily removes ties, which might not be suitable for all scenarios.

<code class="language-sql">SELECT * FROM (
  SELECT empno, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS rnk
  FROM emp
)
WHERE rnk <= 5;</code>

While a non-analytic method using ROWNUM exists, it's less reliable due to potential inconsistencies and is generally less preferred. The analytic function methods above provide more robust and predictable results.

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