SQL to find nth highest salary

Upasana | 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 (n2)

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)

Top articles in this category:
  1. SQL - Write a query to find customers who have no orders yet
  2. Morgan Stanley Java Interview Questions
  3. Citibank Java developer interview questions
  4. Cracking core java interviews - question bank
  5. Goldman Sachs Java Interview Questions
  6. Sapient Global Market Java Interview Questions and Coding Exercise
  7. BlackRock Java Interview Questions

Recommended books for interview preparation:

Find more on this topic:
Buy interview books

Java & Microservices interview refresher for experienced developers.