If you're interested in knowing the highest salary in SQL, you might have already learned how to use the MAX function. But what if you want to find the second or third highest salary? The MAX function alone won't suffice. You'll need another approach to handle this scenario. Let's explore how to find the second and third highest salaries in SQL using a combination of subqueries and common table expressions (CTEs).
To demonstrate, let's assume we have an 'Employees' table with the following structure:
| ID | Name | Salary |
|---|---|---|
| 1 | John Doe | 50000 |
| 2 | Jane Smith | 60000 |
| 3 | Bob Johnson | 55000 |
Finding the Second Highest Salary
The goal here is to find the employee with the second highest salary. To do this, we'll first find the maximum salary, then exclude it from the result set and find the highest remaining salary.
Here's how you can achieve this using a CTE:
Using Common Table Expressions (CTEs)
CTEs allow you to define temporary results sets within a SELECT, INSERT, UPDATE, or DELETE statement. They can make your queries more readable and efficient. Here's how you can use a CTE to find the second highest salary:
WITH SalaryCTE AS ( SELECT Salary, RANK() OVER (ORDER BY Salary DESC) as Rank FROM Employees ) SELECT Salary FROM SalaryCTE WHERE Rank = 2;
Using a Subquery
Alternatively, you can use a subquery to achieve the same result. A subquery is a query nested inside another query. Here's how you can use a subquery to find the second highest salary:
SELECT Salary FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees) ORDER BY Salary DESC LIMIT 1;
Finding the Third Highest Salary
To find the third highest salary, you can apply a similar approach. After excluding the highest and second highest salaries, you'll be left with the third highest. Let's see how to do this using both methods.
Using CTE
You can extend the previous CTE to find the third highest salary like this:
WITH SalaryCTE AS ( SELECT Salary, RANK() OVER (ORDER BY Salary DESC) as Rank FROM Employees ) SELECT Salary FROM SalaryCTE WHERE Rank = 3;
Using a Subquery
Similarly, you can adjust the previous subquery to find the third highest salary:
SELECT Salary FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees) AND Salary > (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees)) ORDER BY Salary DESC LIMIT 1;
SQL is a powerful tool with numerous functions and methods that allow you to analyze and manipulate data in numerous ways. Knowing how to use these techniques can help you to extract meaningful insights from datasets. Now that you've learned how to find the second and third highest salaries in SQL, you can apply these techniques to other scenarios and continue to expand your SQL skills.