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)

Find more on this topic:

Java Interviews

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

Last updated 1 week ago

Subscribe to Interview Questions

Recommended books for interview preparation:

Book you may be interested in..

ebook PDF - Cracking Spring Microservices Interviews for Java Developers