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:
| id | name | salary |
|---|---|---|
| 1 | John Doe | 60000 |
| 2 | Jane Smith | 75000 |
| 3 | Mike Brown | 80000 |
| 4 | Lisa White | 90000 |
| 5 | Tom Black | 60000 |
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.
Sql
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.
Sql
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.
Sql
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:
Sql
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.
