RANK() vs. DENSE_RANK() in Oracle: How Do These Window Functions Differ in Ranking and Handling Nulls?

Oracle RANK() and DENSE_RANK(): A Comparative Analysis of Window Functions

Oracle's RANK() and DENSE_RANK() are window functions used to assign ranks to rows within a specified partition. While both achieve similar goals, their handling of ties and NULL values differs significantly.

RANK() Function

RANK() assigns ranks based on the specified ordering. Crucially, it assigns the same rank to tied rows and then skips ranks. For instance, if three rows are tied for second place, they'll all receive a rank of 2, and the next rank will be 5.

DENSE_RANK() Function

Unlike RANK(), DENSE_RANK() assigns consecutive ranks without gaps, even with ties. All tied rows receive the same rank, and the subsequent rank immediately follows. There are no skipped ranks.

Retrieving the nth Highest Salary

To identify the nth highest salary from the emptbl table, you can employ either RANK() or DENSE_RANK() within the OVER() clause:

<code class="language-sql">SELECT empname, sal
FROM (
  SELECT empname, sal, RANK() OVER (ORDER BY sal DESC) AS rnk  -- Note: ORDER BY sal DESC for nth highest
  FROM emptbl
)
WHERE rnk = n;</code>

Handling NULL Values

The treatment of NULL values by both functions is determined by the ORDER BY clause. If NULLS FIRST is specified, NULLs are ranked before non-NULL values; if NULLS LAST, they are ranked after.

Illustrative Example

Let's examine the following script:

<code class="language-sql">WITH q AS (
  SELECT 10 AS DEPTNO, 'rrr' AS EMPNAME, 10000.00 AS SAL FROM DUAL UNION ALL
  SELECT 11, 'nnn', 20000.00 FROM DUAL UNION ALL
  SELECT 11, 'mmm', 5000.00 FROM DUAL UNION ALL
  SELECT 12, 'kkk', 30000 FROM DUAL UNION ALL
  SELECT 10, 'fff', 40000 FROM DUAL UNION ALL
  SELECT 10, 'ddd', 40000 FROM DUAL UNION ALL
  SELECT 10, 'bbb', 50000 FROM DUAL UNION ALL
  SELECT 10, 'xxx', NULL FROM DUAL UNION ALL
  SELECT 10, 'ccc', 50000 FROM DUAL
)
SELECT EMPNAME, DEPTNO, SAL,
       RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL NULLS FIRST) AS R,
       DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL NULLS FIRST) AS DR1,
       DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL NULLS LAST) AS DR2
FROM q;</code>

The results clearly demonstrate the contrasting behavior of RANK() and DENSE_RANK() when handling NULL values, depending on the NULLS FIRST or NULLS LAST specification in the ORDER BY clause. This example highlights the importance of understanding these nuances when choosing the appropriate ranking function for your specific needs.

The above is the detailed content of RANK() vs. DENSE_RANK() in Oracle: How Do These Window Functions Differ in Ranking and Handling Nulls?. For more information, please follow other related articles on the PHP Chinese website!