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 (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. Morgan Stanley Java Interview Questions
  2. Citibank Java developer interview questions
  3. Cracking core java interviews - question bank
  4. Goldman Sachs Java Interview Questions for Senior Developer
  5. Sapient Global Market Java Interview Questions and Coding Exercise
  6. BlackRock Top Java Interview Questions: Investment Banking Domain
  7. ION Trading Java Interview Questions


Find more on this topic:
Java Interviews image
Java Interviews

Interview - Product Companies, eCommerce Companies, Investment Banking, Healthcare Industry, Service Companies and Startups.

Last updated 1 week ago


Recommended books for interview preparation:

This website uses cookies to ensure you get the best experience on our website. more info