SQL to find nth highest salary

Carvia Tech | July 23, 2020 | 1 min read | 532 views

Each row of Employee needs to be compared to every other row based on salary to figure out the nth highest record, thus the Time Complexity of this operation would be quite high Big O (n²)

SQL nth highest salary

SELECT
    *
FROM
    Employee E1
WHERE
    (N - 1) = (SELECT
            COUNT(DISTINCT (E2.salary))
        FROM
            Employee E2
        WHERE
            E2.salary > E1.salary)

For example, when we want to find out an employee with 2nd higest salary, SQL will be:

SQL - Employee with 2nd highest salary

SELECT
    *
FROM
    Employee E1
WHERE
    (2 - 1) = (SELECT
            COUNT(DISTINCT (E2.salary))
        FROM
            Employee E2
        WHERE
            E2.salary > E1.salary)