Find Nth Lowest Salary

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

  • MAX, which returns the maximum 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 Lowest Salary

SELECT MAX(salary) FROM (SELECT DISTINCT salary FROM employees ORDER BY salary ASC) 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 ascending order with the lowest in the first row.
  • Then, the outer query executes and returns the maximum salary from the first n rows from the inner query result, which is the nth lowest salary from the table.
    • Where "n" must be replaced with the actual value like 2, 3, 4, and so on.

SQL Query for 2nd Lowest Salary

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

SELECT MAX(salary) FROM (SELECT DISTINCT salary FROM employees ORDER BY salary ASC) WHERE ROWNUM < 3;

SQL Query for 3rd Lowest Salary

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

SELECT MAX(salary) FROM (SELECT DISTINCT salary FROM employees ORDER BY salary ASC) WHERE ROWNUM < 4;

SQL Query for 4th Lowest Salary

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

SELECT MAX(salary) FROM (SELECT DISTINCT salary FROM employees ORDER BY salary ASC) WHERE ROWNUM < 5;

and so on.

Overall

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

Related Links