Hi! Today's post is about a popular interview question, which is the sql query to find the second highest salary. Most freshers would have faced this question in their interviews. There is not one single solution for this. You can approach in different ways to get the second maximum salary from the database. Here I'm going to show you some of the best possible ways you can write the sql query to fetch the salary that is second highest.
I've tried these queries on MySQL but it also works with other relational databases such as MSSQL Server, Oracle etc., that uses SQL.
Finding the Second Highest Salary:
Consider the following table. It consists of a set of employee records and we are going to use in our queries to get salary that is second largest.
Table Name: Employees
| ID | Name | Designation | Salary |
|---|---|---|---|
| 1 | Colleen Hurst | Regional Director | 205500 |
| 2 | Garrett Winters | Accountant | 170750 |
| 3 | Quinn Flynn | Support Lead | 342000 |
| 4 | Jena Gaines | Software Engineer | 133600 |
| 5 | Brielle William | Regional Director | 372000 |
1. Using Subquery:
Here is the simple query to find the highest salary from the above 'Employees' table. It uses the max() aggregate function to return the maximum value of a field (or expression).
SELECT MAX(Salary) FROM Employees // output: 372000
Simply nesting this query will give you the second highest salary. It excludes the maximum salary from the table using subquery and fetches the maximum salary again.
SELECT MAX(Salary) FROM Employees WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employees) // output: 342000
Below is the slightly different version of this sub query and it uses < (less than) operator instead of NOT IN
SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees) // output: 342000
2. Using Limit Clause:
To find the second highest salary without sub-query, you can use use LIMIT and ORDER BY clause.
SELECT Salary FROM Employees ORDER BY Salary DESC LIMIT 1,1 // Output: 342000
The query will simply sort the salary field in descending order and fetch the second row (2nd largest) from the result set. If you have duplicate values in salary column, then use DISTINCT to select only unique values.
You can generalize the limit query to find out the n-th highest salary like this,
SELECT DISTINCT(Salary) FROM Employees ORDER BY Salary DESC LIMIT (n-1),1
3. With Self Join:
Another interesting way to write the query is to use self join. It takes up the cross-product of the table with itself, exclude the highest salary from the lot and then get the maximum salary from the remaining rows - which gives you the second-maximum.
Select MAX(Emp1.Salary) FROM Employees Emp1, Employees Emp2 WHERE Emp1.Salary < Emp2.Salary // output: 342000Read Also:
Those are some of the popular ways to find the second maximum salary. Not only for salary, but the concept can be generalized to find age or other similar fields. I hope this tutorial is useful to you. Please share this post on social media if you like it.
No comments:
Post a Comment