Finding the Nth Highest Value in a Column

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 the 3rd highest salary

How it works: This query utilizes a Common Table Expression (CTE) named `RankedSalaries` to first assign a rank to each salary using the `DENSE_RANK()` window function. `DENSE_RANK()` assigns consecutive ranks to rows within a partition (here, the entire table) based on the specified order, handling ties by giving them the same rank. The outer query then simply filters for the desired `rank_num` (e.g., 3 for the 3rd highest salary).