How Can You Find the Second Highest Salary in SQL?

Finding the second highest salary in a SQL database is a common question asked during technical interviews. It tests your understanding of SQL queries and how to manipulate data effectively. In this article, we will explore various approaches to solve this problem and provide clear code examples to illustrate each method.

Suppose you have a table named Employees, which contains the following columns: id, name, and salary. Here is a sample structure of the Employees table:

To find the second highest salary, you can consider various techniques. Let’s explore some of these methods through SQL queries.

Method 1: Using the DISTINCT and ORDER BY Clauses

One of the simplest ways to retrieve the second highest salary is to list all unique salaries, order them in descending order, and then limit the results to the second one.

1SELECT DISTINCT salary
2FROM Employees
3ORDER BY salary DESC
4LIMIT 1 OFFSET 1;

In this query, DISTINCT ensures we only get unique salary values. The ORDER BY salary DESC sorts the salaries from highest to lowest, and LIMIT 1 OFFSET 1 skips the first highest salary and returns the next one, which is the second highest.

Method 2: Using a Subquery

Another approach is to use a subquery to find the maximum salary that is less than the highest salary.

1SELECT MAX(salary) AS SecondHighestSalary
2FROM Employees
3WHERE salary < (SELECT MAX(salary) FROM Employees);

In this example, the inner query (SELECT MAX(salary) FROM Employees) retrieves the highest salary. The outer query then finds the maximum salary that is less than this value.

Method 3: Using the ROW_NUMBER() Window Function

If you are using a database that supports window functions, you can also achieve this using the ROW_NUMBER() function. This method is efficient for larger datasets.

1WITH RankedSalaries AS (
2    SELECT salary,
3           ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
4    FROM Employees
5    GROUP BY salary
6)
7SELECT salary
8FROM RankedSalaries
9WHERE rank = 2;

In this query, the ROW_NUMBER() function assigns a rank to each salary in descending order. We then select the salary where the rank is 2, giving us the second highest salary.

Method 4: Using LIMIT and ORDER BY with a Regular Query

A simple method, depending on the SQL dialect, is to select the salaries ordered in descending order and take the second one directly:

1SELECT salary
2FROM Employees
3ORDER BY salary DESC
4LIMIT 1 OFFSET 1;

This method is straightforward and achieves the same result effectively.

Understanding how to find the second highest salary in SQL involves knowing various SQL clauses, functions, and techniques. By practicing these methods, you become more comfortable with SQL and enhance your skill set for technical interviews. Each of these examples is effective, and the choice of method may depend on the specific requirements or constraints of the database system you are working with.