RANK() vs. DENSE_RANK() in Oracle: How to Find the Nth Salary and Handle Null Values?

Oracle's RANK() and DENSE_RANK(): Finding the Nth Highest Salary

This article explores the differences between Oracle's RANK() and DENSE_RANK() functions, demonstrating their use in identifying the nth highest salary and handling potential null values.

Understanding RANK() and DENSE_RANK()

Both functions assign ranks within ordered data sets (partitions). The key difference lies in their handling of ties:

• RANK(): Assigns the same rank to tied values, leaving gaps in the ranking sequence. If three employees share the second rank, the next rank will be 5.

• DENSE_RANK(): Also assigns the same rank to tied values, but without gaps. If three employees share the second rank, the next rank will be 3.

Extracting the Nth Highest Salary

The following query uses RANK() to find the nth highest salary:

<code class="language-sql">SELECT *
FROM (
    SELECT emp.*, RANK() OVER (ORDER BY sal DESC) as salary_rank
    FROM emptbl emp
) ranked_salaries
WHERE salary_rank = n;</code>

Replace n with the desired rank (e.g., 3 for the third-highest salary). Note the ORDER BY sal DESC clause, crucial for descending order ranking.

Null Value Considerations

The treatment of NULL values depends on the ORDER BY clause:

• ORDER BY sal NULLS FIRST: NULL salaries are ranked first.

• ORDER BY sal NULLS LAST: NULL salaries are ranked last.

Example: Consider a table with NULL salaries. Using NULLS LAST, a query for the top three salaries would exclude employees with NULL salaries from the top three. Conversely, NULLS FIRST would include them, potentially placing them at the top of the ranking.

For a comprehensive guide and further examples, please refer to this resource: Detailed Explanation and Examples

The above is the detailed content of RANK() vs. DENSE_RANK() in Oracle: How to Find the Nth Salary and Handle Null Values?. For more information, please follow other related articles on the PHP Chinese website!