SQL
Find Nth Highest Value Using DENSE_RANK()
Discover how to efficiently retrieve the Nth highest value, such as the Nth highest salary, from a dataset using the DENSE_RANK() window function in SQL, perfect for ranking-based queries.
WITH RankedSalaries AS (
SELECT
employee_id,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM
employees
)
SELECT
employee_id,
salary
FROM
RankedSalaries
WHERE
rank_num = 3; -- For 3rd highest salary
How it works: This snippet uses a Common Table Expression (CTE) and the `DENSE_RANK()` window function to find the Nth highest value (e.g., the 3rd highest salary). `DENSE_RANK()` assigns a rank to each salary, handling ties by giving them the same rank and not skipping subsequent ranks. The outer query then filters for the desired rank number.