Hello guys, if you have attended programming or software development interview then you may have come across SQL queries. In the past, I have shared popular SQL queries from interviews and one of them is how to find the 2nd highest salary of employee in SQL. This is probably the most popular SQL query question I have come across in my career and I have seen it many times. When I first come across this one I have hard time to solve it but over the years, I have learned several ways to solve this problem and find the 2nd highest or even 3rd highest salary in SQL. Earlier, I have shared how to find Nth highest salary and in this article, I am going to share 5 different ways to find the second highest salary in SQL.
You can try them to get familiar with not just this question but the concept you will touch by solving this problem like subquery, EXISTS, window function and much more.
So what are we waiting for, let's jump into problem and solution
5 Ways to find the 2nd Highest Salary of Employee in SQL
So, you are given an employee table which has a salary column, which may or may not contain duplicates, it can also contain null and you need to write SQL query to print the employee who earn highest salary.
Simple problem right? well its not that simple if you are not familiar with essential SQL concepts like co-related subqueries, EXISTS and NOT EXISTS clause, Window Function etc.
Anyway, here are 5 different ways to calculate the second highest salary in SQL:
1. Second highest Salary Using a subquery
This is the simplest way to find the second highest salary in SQL and this was also my solution when this question was asked to me first time 10 years ago. In this method we will use a subquery to find the maximum salary from the employee table, and then selects all the salaries that are not equal to the maximum salary. The result is the second highest salary.
This is also the easiest and most intuitive way to find the second highest salary and you can easily understand the logic, unlike few methods which we will see soon.
SELECT MAX(salary)
FROM employee
WHERE salary NOT IN (SELECT MAX(salary) FROM employee);2. How to find Second Highest Salary using a self-join
This is one of the most common method to find the second highest salary of employee in SQL. In this method we will use a self-join to join the employee table with itself, based on the salary column.
The join condition is such that the salary of the first table is less than the salary of the second table. The result is the second highest salary.
Again, you may find it difficult to understand but what we are doing here is comparing two tables row by row and we are trying to find all the salary which are lesser than the salary of second table which means the salary which we will find will be the 2nd highest one.
SELECT e1.salary FROM employee e1
JOIN employee e2
ON e1.salary < e2.salary
GROUP BY e1.salary
ORDER BY e1.salary DESC
LIMIT 1;3. Second Highest Salary Using DISTINCT and ORDER BY
In this example, we will use the DISTINCT, ORDER BY and LIMIT keyword to find the 2nd highest salary in SQL. It first selects all the distinct salary values from the employee table, orders them in descending order, and then selects the second row from the result.
This method will not work in all database because it uses LIMIT keyword which may or may not be supported in all database. As far I know, LIMIT is supported by MySQL database and most likely supported by PostgreSQL as well.
SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC LIMIT 1,1;4. Second Highest Salary Using NOT EXISTS:
In this example, we will use uses a subquery with NOT EXISTS operator to find the salary whose value is not present in the salary column of the employee table where salary is greater than the current salary. This query may seem a bit tough to understand but its quite fast and suitable for table with large amount of data.
SELECT salary FROM employee e1
WHERE NOT EXISTS (SELECT 1 FROM employee e2 WHERE e2.salary > e1.salary);5. 2nd Highest Salary Using window functions:
In this method, we will use a window function RANK() to assign a rank to each salary based on its value in descending order. Then it selects the salary whose rank is 2. If you are not familiar with RANK it will assign the same rank to two rows if they are equal. This is also a generic method to find the second highest salary in any database.
SELECT salary FROM (
SELECT salary, RANK() OVER (ORDER BY salary DESC) as rank
FROM employee
) sub
WHERE rank = 2;And, if you want to find the 3rd, 4th or 5th highest salary in SQL, you can use the same logic which I have also explained in my earlier post about SQL query to find the Nth highest salary. Just practice and you will realize its easier than you think.
That's all about 5 ways to find the 2nd highest salary in SQL. As I said this is one of the popular SQL interview question and you must prepare for it. By the way, a couple of examples will not work in all database, particularly the LIMIT keyword one which will only work in MySQL and PostgreSQL which support LIMIT Keyword, but sub-query and self join and window function solution will work in all database.
It's also important to note that the performance of these methods may vary depending on the size and structure of the table, indexes, and the specific database you are using. For example, EXISTS example may be faster and more suitable if you are running the query in a database which have millions of records, but you must analyze the performance before running any query in production.
You can do so by first running your query on a copy of production database. This is the best practice I follow before releasing any SQL code into production. I asked DBAs to create a copy of production database and then I run my query and analyze the performance. Since UAT and Production database may not be the same in terms of data and indexes, just running the SQL query in UAT is not enough, you must run it on production copy if you want a safe release.
- Difference between UNION and UNION ALL in SQL
- 10 Examples of ALTER command in SQL
- MySQL vs NoSQL comparison
- Difference between Self and Equi join in SQL
- What are T-SQL Window Functions?
- How to use Stored Procedure in SQL?
- 4 Examples of CASE expression in SQL Server
- How to create and rollback transaction in database?
- How to convert String to Date in SQL?
- How to use WHERE and HAVING clause in SQL
- How to find top 10 Records in SQL
- Difference between Truncate, Drop, and Delete in SQL
1 comment: