How to find Nth highest salary from a table in SQL?

The nth highest salary In SQL is the salary that is greater than or equal to the (n-1)th highest salary. There are several ways to find the nth highest salary from a table in SQL. We will discuss the following approaches in this article:

• LIMIT and OFFSET with subquery 
• DENSE_RANK() with subquery 
• ROW_NUMBER() with subquery 
• Self-join with subquery 
• a temporary table or common table expression (CTE)
• subquery with TOP (for SQL Server)
• LIMIT with subquery using a nested select
• OFFSET with FETCH and ORDER BY (SQL:2008 Standard)
• MAX() and subquery
• subquery with NOT IN

Example table:

Approach 1: Using Subquery with LIMIT and OFFSET

This is the simplest approach. It involves using a subquery to find the (n-1)th highest salary, and then using the LIMIT and OFFSET clauses to return the nth highest salary.

Example:

In this query, the subquery first finds the second highest salary in SQL by ordering the salaries in descending order and then returning the first row. The outer query then selects all the employees whose salaries are equal to or greater than the second highest salary.

Approach 2: Using Subquery with DENSE_RANK()

The DENSE_RANK() function is a window function that returns the rank of a row within a group of rows, without gaps. This means that two rows with the same salary will both be assigned the same rank.

The following is an example of how to use the DENSE_RANK() function to find the nth highest salary:

In this query, the DENSE_RANK() function is used in the WINDOW clause to calculate the rank of each row within its department. The ROWS BETWEEN 2 PRECEDING AND CURRENT ROW clause specifies that the nth highest salary is the row with the rank of 2.

Approach 3: Using Subquery with ROW_NUMBER()

The ROW_NUMBER() function is another window function that returns the row number of a row within a group of rows. Unlike the DENSE_RANK() function, the ROW_NUMBER() function does not skip any rows with the same salary.

The following is an example of how to use the ROW_NUMBER() function to find the nth highest salary:

In this query, the ROW_NUMBER() function is used in the WINDOW clause to calculate the row number of each row within its department. The ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING clause specifies that the nth highest salary is the row with the row number of 2.

Approach 4: Using Subquery with Self-Join

A self-join is a join between a table and itself. This approach can be used to find the nth highest salary by first joining the table to itself on the salary column. The nth highest salary can then be found by using the WHERE clause to select the row with the (n-1)th highest salary.

Example:

In this query, the table is first joined to itself on the salary column. The WHERE clause then selects the row in the first table where the number of rows in the second table with higher salaries is equal to 2. This row will have the second highest salary.

Approach 5: Using a Temporary Table or CTE

A temporary table is a table that is created for the duration of a single query. A CTE is a named temporary table that can be used in multiple queries.

Both temporary tables and CTEs can be used to find the nth highest salary. The Example:

In this query, a temporary table is created to store the salaries in descending order. The second highest salary can then be found by selecting the row with the row number of 2.

The following is an example of how to use a CTE:

In this query, a CTE is used to create a temporary table to store the salaries in descending order. The second highest salary can then be found by selecting the row with the row number of 2.

Approach 6: Using Subquery with TOP (For SQL Server)

The TOP clause is a special clause that can be used to return a limited number of rows from a query. In SQL Server, the TOP clause can be used to find the nth highest salary by specifying the nth row in the result set.

The following is an example of how to use the TOP clause to find the nth highest salary in SQL Server:

In this query, the TOP clause is used to return the top 2 rows from the employees table, ordered by salary in descending order. The first row in the result set will be the second highest salary.

Approach 7: Using LIMIT with Subquery using a Nested Select

The LIMIT clause can also be used to find the nth highest salary in other SQL dialects, such as MySQL and PostgreSQL. However, the LIMIT clause cannot be used to return a row number.

In this case, we can use a nested select to find the (n-1)th highest salary, and then use the LIMIT clause to return the next row.

Example:

In this query, the subquery first finds the second highest salary by ordering the salaries in descending order and then returning the first row. The outer query then selects all the employees whose salaries are equal to or greater than the second highest salary, and then limits the result set to 1 row.

Approach 8: Using OFFSET with FETCH and ORDER BY (SQL:2008 Standard)

The OFFSET and FETCH clauses can be used to find the nth highest salary in SQL:2008 Standard. The OFFSET clause specifies the number of rows to skip. The FETCH clause specifies the number of rows to return.

The following is an example of how to use the OFFSET and FETCH clauses to find the nth highest salary:

In this query, the first two rows are skipped using the OFFSET clause. The next row is retrieved using the FETCH clause. The first row in the result set will be the second highest salary.

Approach 9: Using MAX() and Subquery

The MAX() function returns the maximum value in a column. By first finding the (n-1)th highest salary and then using the MAX() tool to find the next highest salary, this method can be used to find the nth highest salary.

Example:

In this query, the subquery first finds the second highest salary by ordering the salaries in descending order and then returning the maximum value. The outer query then selects all the employees whose salaries are greater than the second highest salary

Approach 10: Using Subquery with NOT IN

The NOT IN operator returns all the rows that do not contain a value in the specified subquery. This approach can be used to find the nth highest salary by first finding the (n-1)th highest salary, and then using the NOT IN operator to find the next highest salary.

Example:

In this query, the subquery first finds the second highest salary by ordering the salaries in descending order and then returning the maximum value. The outer query then selects all the employees whose salaries are not equal to the second highest salary.

Conclusion

These are the 10 approaches to find the nth highest salary from a table in SQL. The best approach to use will depend on the specific database and the requirements of the query.

Here are some additional tips for finding the nth highest salary:

• Use the appropriate data type for the salary column. For example, if the salaries are integers, then you should use the INT data type.
• Use the correct order by clause. The order by clause should be used to order the salaries in descending order.
• Use the appropriate window function. The window function should be used to calculate the rank or row number of each row.
• Use the appropriate LIMIT or OFFSET clause. The LIMIT or OFFSET clause should be used to limit the number of rows returned by the query.