How to find the nth highest or lowest salary from a table in SQL (Basic 1 - 12)

There are a few different ways to find the nth highest or lowest salary from a table in SQL. One way is to use a subquery. Another way is to use an analytic function, such as ROW_NUMBER() or RANK().

Using a subquery

The following SQL query uses a subquery to find the nth highest salary from the Employees table:

SQL

SELECT *
FROM Employees
WHERE Salary IN (
    SELECT Salary
    FROM Employees
    ORDER BY Salary DESC
    LIMIT 1 OFFSET n - 1
);

The subquery in the WHERE clause returns the top n salaries from the Employees table, ordered by salary in descending order. The OFFSET clause is used to skip the first n - 1 salaries, so that the subquery only returns the nth highest salary.

Using an analytic function

The following SQL query uses the ROW_NUMBER() analytic function to find the nth highest salary from the Employees table:

SQL

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rn
    FROM Employees
) AS EmployeesWithRowNumbers
WHERE Rn = n;

The ROW_NUMBER() function assigns a ranking to each row in a table, starting with 1. The PARTITION BY clause is used to partition the table by department before ranking the rows. The ORDER BY clause is used to order the rows within each partition by salary in descending order. The WHERE clause filters the results to only include the row with the rank of n.

Finding the nth lowest salary

The following SQL query uses a subquery to find the nth lowest salary from the Employees table:

SQL

SELECT *
FROM Employees
WHERE Salary IN (
    SELECT Salary
    FROM Employees
    ORDER BY Salary ASC
    LIMIT 1 OFFSET n - 1
);

The subquery in the WHERE clause returns the bottom n salaries from the Employees table, ordered by salary in ascending order. The OFFSET clause is used to skip the first n - 1 salaries, so that the subquery only returns the nth lowest salary.

The following SQL query uses the ROW_NUMBER() analytic function to find the nth lowest salary from the Employees table:

SQL

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary ASC) AS Rn
    FROM Employees
) AS EmployeesWithRowNumbers
WHERE Rn = n;

The ROW_NUMBER() function assigns a ranking to each row in a table, starting with 1. The PARTITION BY clause is used to partition the table by department before ranking the rows. The ORDER BY clause is used to order the rows within each partition by salary in ascending order. The WHERE clause filters the results to only include the row with the rank of n.

Conclusion

There are a few different ways to find the nth highest or lowest salary from a table in SQL. The best method to use will depend on your specific needs and the database you are using.