RANK() vs. DENSE_RANK() in Oracle: What's the Difference?

Oracle RANK() and DENSE_RANK(): A Detailed Comparison

Oracle's RANK() and DENSE_RANK() functions both assign ranks to rows based on specified criteria, but their ranking methodologies differ significantly. This article clarifies these differences and illustrates their practical applications.

RANK() Function: Skipping Ranks for Ties

The RANK() function assigns ranks hierarchically. Rows with identical values receive the same rank, but subsequent ranks are skipped. For example, if three rows share the highest value, they'll all be ranked 1, and the next rank will be 4 (skipping 2 and 3).

DENSE_RANK() Function: Consecutive Ranks

DENSE_RANK() provides consecutive ranks, even when ties exist. Using the same example, the three rows with the highest value would be ranked 1, 2, and 3, respectively, maintaining a continuous rank sequence.

Illustrative Example: Finding the Nth Highest Salary

Consider an employee table (emptbl) to find the third-highest salary. Both functions can be used, but the results might vary:

SELECT empname, sal
FROM emptbl
ORDER BY sal DESC
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;

This SQL utilizes OFFSET and FETCH for efficient pagination. The choice between RANK() and DENSE_RANK() here depends on whether you want to skip ranks in case of salary ties.

Handling NULL Values

The handling of NULL values depends on the NULLS FIRST or NULLS LAST clause within the ORDER BY statement. NULLS FIRST ranks NULL values before non-NULL values, and vice-versa for NULLS LAST.

Choosing the Right Function

Use RANK() when distinct ranks are necessary, even with ties. DENSE_RANK() is preferable when consecutive ranks are required, regardless of tied values. The selection depends entirely on the specific analytical requirements.

Further Learning

For a more comprehensive understanding, consult the official Oracle documentation:

• Oracle RANK() Function
• Oracle DENSE_RANK() Function
• RANK() and DENSE_RANK() Examples

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