What's the Difference Between RANK() and DENSE_RANK() in Oracle SQL?

Oracle SQL: Understanding RANK() and DENSE_RANK()

This article clarifies the key differences between the RANK() and DENSE_RANK() functions in Oracle SQL, focusing on their ranking behavior, particularly when dealing with ties and NULL values.

Key Differences:

Both functions assign ranks to rows within a dataset based on specified ordering. The crucial difference lies in their handling of ties:

• RANK(): Assigns the same rank to tied rows, resulting in gaps in the ranking sequence. If multiple rows share a rank, the next rank skips over the tied ranks. For instance, if three rows tie for second place, they'll all receive a rank of "2", and the next rank will be "5".

• DENSE_RANK(): Assigns consecutive ranks without gaps, even with ties. There's no skipping of ranks; the ranking sequence remains continuous.

Practical Application: Finding the nth Highest Salary

To retrieve the nth highest salary from the emptbl table using RANK(), you could use a query similar to this (though more efficient methods exist):

<code class="language-sql">SELECT empname
FROM (SELECT empname, RANK() OVER (ORDER BY sal DESC) as sal_rank FROM emptbl)
WHERE sal_rank = n;</code>

This ranks salaries in descending order and filters for the row with the desired rank.

NULL Value Handling:

The treatment of NULL values depends on the ORDER BY clause. If NULLs are ordered first (ORDER BY sal NULLS FIRST), they'll receive the lowest rank. If ordered last (ORDER BY sal NULLS LAST), they'll receive the highest rank.

Illustrative Example:

Let's examine this with sample data:

<code>DEPTNO  EMPNAME    SAL
------------------------------
10       rrr    10000.00
11       nnn    20000.00
11       mmm    5000.00
12       kkk    30000.00
10       fff    40000.00
10       ddd    40000.00
10       bbb    50000.00
10       ccc    50000.00</code>

The query:

<code class="language-sql">SELECT empname, deptno, sal,
       RANK() OVER (PARTITION BY deptno ORDER BY sal NULLS FIRST) as rnk,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal NULLS FIRST) as drnk_first,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal NULLS LAST) as drnk_last
FROM emptbl;</code>

Produces results clearly showing the difference: rnk demonstrates rank skipping, while drnk_first and drnk_last show consecutive ranking with NULLs handled differently. The output table would visually represent the distinct ranking behaviors. (Note: The example assumes a table named emptbl exists with the provided data; replace with your actual table name.)

The above is the detailed content of What's the Difference Between RANK() and DENSE_RANK() in Oracle SQL?. For more information, please follow other related articles on the PHP Chinese website!