SQL to find employee details with nth highest salary

Carvia Tech | May 25, 2019 | 1 min read | 1 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 Investment Banking 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. ION Trading Java Interview Questions
  7. Top 50 Multi-threading Java Interview Questions for Investment Bank



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