How do you find the Nth highest salary in SQL?

| id | name    | salary |
|----|---------|--------|
|  1 | Alice   | 95000  |
|  2 | Bob     | 120000 |
|  3 | Charlie | 85000  |
|  4 | Diana   | 135000 |
|  5 | Eve     | 120000 | ← same as Bob
|  6 | Frank   | 75000  |

-- Find the 2nd highest salary
WITH ranked AS (
    SELECT name, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT name, salary
FROM ranked
WHERE rnk = 2;

-- Result: Bob | 120000, Eve | 120000 (both rank 2)

-- Generic: replace 2 with any N
WITH ranked AS (
    SELECT name, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT name, salary FROM ranked WHERE rnk = &N;

-- 2nd highest (distinct values)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;  -- skip 1 (highest), return next

-- Nth highest (replace OFFSET 1 with N-1)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET (N - 1);
-- For N=3: OFFSET 2 → skips top 2 unique salaries → returns 3rd

-- Find Nth highest: find salary where exactly N-1 salaries are greater
SELECT DISTINCT salary
FROM employees e1
WHERE N - 1 = (
    SELECT COUNT(DISTINCT salary)
    FROM employees e2
    WHERE e2.salary > e1.salary
);

-- For N=2 (2nd highest):
SELECT DISTINCT salary
FROM employees e1
WHERE 1 = (
    SELECT COUNT(DISTINCT salary)
    FROM employees e2
    WHERE e2.salary > e1.salary
);
-- salary = 120000 (exactly 1 salary [135000] is greater)

-- 2nd highest salary
SELECT MAX(salary)
FROM employees
WHERE salary NOT IN (SELECT MAX(salary) FROM employees);

-- 3rd highest salary (repeat)
SELECT MAX(salary)
FROM employees
WHERE salary NOT IN (
    SELECT MAX(salary) FROM employees
    UNION
    SELECT MAX(salary) FROM employees WHERE salary != (SELECT MAX(salary) FROM employees)
);
-- Messy for higher N — use DENSE_RANK instead!

-- Return NULL if Nth salary doesn't exist
WITH ranked AS (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT COALESCE(MAX(salary), NULL) AS nth_salary
FROM ranked
WHERE rnk = 5;  -- If only 3 distinct salaries → returns NULL

-- Nth LOWEST salary
WITH ranked AS (
    SELECT name, salary,
           DENSE_RANK() OVER (ORDER BY salary ASC) AS rnk  -- ASC for lowest
    FROM employees
)
SELECT name, salary FROM ranked WHERE rnk = 2;

-- Nth highest within a department
WITH ranked AS (
    SELECT name, department, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT name, department, salary FROM ranked WHERE rnk = 2;