Find Nth Highest Salary

We need to use the below keywords to find the Nth highest salary from an employee table.

  • MIN, which returns the minimum value of a column
  • DISTINCT, which returns a list of unique or distinct values of a column
  • ROWNUM, which defines a sequential number for the result set, that starts from 1 for the first row in the result set.

Let's assume a table with the below table definition and data, where we need to find the second highest salary.

ID     Name     Salary
===========================
1    Arun       20000
2    Kiran      15000
3    James      35000
4    Sammy      50000
5    Martin     25000

SQL Query for Nth Highest Salary

SELECT MIN(salary) FROM (SELECT DISTINCT salary FROM employees ORDER BY salary DESC) WHERE ROWNUM < n+1;

In the above query, we have an inner query within the outer query, so the inner query executes first and is followed by the outer query as explained below.

  • The inner query executes and returns the list of distinct salary amounts from the table, in descending order with the highest in the first row.
    • First, it sorts the rows descending based on salary.
    • Then, it returns the distinct salary amounts, with the highest in the first row (having ROWNUM = 1), the second highest in the second row (with ROWNUM = 2), and so on.
  • Then, the outer query executes and returns the minimum salary from the first n rows from the inner query result, which is the nth highest salary from the table.
    • It finds the minimum salary among the rows having ROWNUM < n+1, which picks up the value from the nth row, which is the Nth highest salary.

SQL Query for 2nd Highest Salary

We can use the same query to find the 2nd highest salary from the table by replacing "n" with 2 as shown below.

SELECT MIN(salary) FROM (SELECT DISTINCT salary FROM employees ORDER BY salary DESC) WHERE ROWNUM < 3;

SQL Query for 3rd Highest Salary

We can use the same query to find the 3rd highest salary from the table by replacing "n" with 3 as shown below.

SELECT MIN(salary) FROM (SELECT DISTINCT salary FROM employees ORDER BY salary DESC) WHERE ROWNUM < 4;

SQL Query for 4th Highest Salary

We can use the same query to find the 4th highest salary from the table by replacing "n" with 4 as shown below.

SELECT MIN(salary) FROM (SELECT DISTINCT salary FROM employees ORDER BY salary DESC) WHERE ROWNUM < 5;

and so on.

Another Way - Without using MIN or MAX keywords

We can even use the below keywords to find the Nth highest salary from an employee table.

  • TOP, which returns a specific number of top rows from the result set
  • ORDER BY, which sorts the rows either ascending or descending based on a specific column

SQL Query for Nth Highest Salary

SELECT TOP 1 salary FROM (SELECT TOP n salary FROM employees ORDER BY salary DESC) ORDER BY salary ASC;

In the above query, we have an inner query within the outer query, so the inner query executes first and is followed by the outer query as explained below.

  • The inner query executes and returns the list of top n rows after sorting the rows descending based on salary, with the highest in the first row.
    • First, it sorts the rows descending based on salary.
    • Then, it returns the top n (n must be replaced with actual values like 2, 3, 4, and so on) rows, with the highest in the first row and Nth highest in the last row.
  • Then, the outer query executes and returns the Nth highest salary from the table as below.
    • First, it sorts the rows ascending based on salary, with the Nth highest in the first row.
    • Then, it returns the top 1 row, which is the Nth highest.

SQL Query for 2nd Highest Salary

We can use the same query to find the 2nd highest salary from the table by replacing "n" with 2 as shown below.

SELECT TOP 1 salary FROM (SELECT TOP 2 salary FROM employees ORDER BY salary DESC) ORDER BY salary ASC;

SQL Query for 3rd Highest Salary

We can use the same query to find the 3rd highest salary from the table by replacing "n" with 3 as shown below.

SELECT TOP 1 salary FROM (SELECT TOP 3 salary FROM employees ORDER BY salary DESC) ORDER BY salary ASC;

SQL Query for 4th Highest Salary

We can use the same query to find the 4th highest salary from the table by replacing "n" with 4 as shown below.

SELECT TOP 1 salary FROM (SELECT TOP 4 salary FROM employees ORDER BY salary DESC) ORDER BY salary ASC;

and so on.

Overall

We now know the SQL Query to find the Nth highest salary from an employee table.

Related Links