SQL

Finding the Nth Highest Value Using DENSE_RANK() Window Function

Efficiently retrieve the Nth highest value from a dataset using SQL's DENSE_RANK() window function, ideal for ranking, leaderboard generation, and top-N analyses.

SELECT
    employee_id,
    employee_name,
    salary
FROM (
    SELECT
        employee_id,
        employee_name,
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) as salary_rank
    FROM
        employees
) AS RankedSalaries
WHERE
    salary_rank = 3; -- Change '3' to find the Nth highest value

How it works: This query uses the `DENSE_RANK()` window function within a subquery to assign a rank to each employee based on their salary in descending order. `DENSE_RANK()` assigns consecutive ranks without gaps, even for ties. The outer query then selects all employees who hold the specified Nth rank (in this case, the 3rd highest salary). This method is robust for finding top-N or Nth values while handling ties.

Finding the Nth Highest Value Using DENSE_RANK() Window Function

Need help integrating this into your project?