How to Find Second Highest Salary in SQL
It's crucial to master SQL queries for managing and analyzing databases. This article focuses on finding the second-highest salary in SQL, which is a common yet important task in database operations. It covers various methods that are suitable for different SQL environments, providing practical insights to help readers optimize their database queries.
The task is to find the second-highest salary on that table. For the purpose of understanding, let’s create a table using the below SQL command:
-- create a table named employees CREATE TABLE employees ( name TEXT NOT NULL, salary INTEGER NOT NULL );
The above code will create an empty table named employees with two rows, namely, name and salary.
Now let’s insert some data to the table so that we can make queries to the table using the below SQL commands:
-- insert data into employees table INSERT INTO employees VALUES ('Amar', 200000); INSERT INTO employees VALUES ('Akbhar', 5000000) INSERT INTO employees VALUES ('Anthony', 7000000); INSERT INTO employees VALUES ('Mike', 6000000) INSERT INTO employees VALUES ('Harvey', 4000000);
To check out the employees table at this point, use the below SQL command:
SELECT * FROM employees;
Output:
Clearly, Mike has the 2nd highest salary in this case. But as the data set becomes bigger, merely observing the data and filtering the 2nd highest salary can be hectic. Therefore, coming back to the problem statement, let’s design an SQL query to find the second-highest salary in the employee's table.
You can also explore – Introduction to Data Analysis Using SQL
Now, we will see the steps on how to find 2nd highest salary in SQL.
SQL Query to Find the Second Highest Salary.
Below is the list of methods that can be used to find the second-highest salary.
Method 1: Excluding the highest Salary
This method involves making a subquery that excludes the maximum salary from the query dataset and finds the next highest salary in the new data set after excluding the highest salary that technically is the second highest salary in the employee's table. Use the below SQL query for the same:
SELECT MAX(SALARY) FROM employees WHERE SALARY < (SELECT MAX(SALARY) FROM employees);
Output:
Method 2: Using Correlated SubQuery
This method makes use of correlated subqueries. This solution can also be used to get the Nth highest salary in the employees table. The basic idea is that for each processed record by the outer query, the inner query gets executed and returns the records with a salary less than that of the current salary. In our case, we need the second highest salary, so we will stop our query as soon as the query inside returns the value 2.
SELECT salary FROM employees e WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM employees p WHERE e.salary<=p.salary);
Output:
Must Read: Understand the Subquery in SQL
Method 3: Using LIMIT Clause
This method involves making use of the LIMIT query to get the second-highest salary from the employees table. The LIMIT clause can be used to query the first few rows, last few rows, or rows within a range. Here we will also use the SORT BY clause to sort the query set. So the SQL query goes as follows:
SELECT salary FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2) AS emp ORDER BY salary LIMIT 1;
Output:
You can also explore – SQL Tutorial for Beginners
Must Check: How to Use Limit Clause in SQL
Conclusion:
In this article, we explored the following methods to find the second-highest salary in a table of employees:
- Excluding the highest salary from the query set.
- Using correlated subquery
- Using the LIMIT clause
Conclusion
Finding the second-highest salary in SQL is a common task that demonstrates the versatility of SQL querying. We've explored various methods, including subqueries, window functions, and database-specific keywords, adaptable to SQL Server, MySQL, or PostgreSQL. This guide is useful for database management, SQL programming, and interview preparation. Understanding these techniques is key to tackling complex queries and enhancing SQL proficiency.
FAQs on How to Find the 2nd Highest Salary using SQL
What are some methods to find the second highest salary in SQL?
- Using ORDER BY and LIMIT Clause: This method involves selecting distinct salaries from the Employee table, ordering them in descending order, and using the LIMIT clause to skip the first salary and return the second.
- Using Subqueries: This approach finds the maximum salary from the Employee table that is not the highest salary.
- Using the RANK() function: This method assigns a rank to each salary and then selects the salary with rank 2.
How can I find the second highest salary using a subquery with NOT EXISTS operator?
A subquery with the NOT EXISTS operator can be used to find a salary value that doesn't have any higher salaries in the table.
Is there a method to find the second highest salary without using subqueries or ORDER BY?
Yes, you can use the MAX function within a WHERE clause that filters salaries less than the highest salary in the table.
How to find the second highest salary in SQL Server using the TOP keyword?
In SQL Server, you can use the TOP keyword with a nested query to order salaries in descending order and then select the top 2 salaries.
Can the LIMIT keyword be used in all SQL databases?
The LIMIT keyword is primarily used in MySQL and PostgreSQL. You might need to use different methods like sub-queries or window functions for other databases.
How can I find the Nth highest salary using these methods?
The mentioned methods can be adapted to find the Nth highest salary by adjusting the LIMIT or OFFSET values or by changing the rank value in the RANK() function.
Vikram has a Postgraduate degree in Applied Mathematics, with a keen interest in Data Science and Machine Learning. He has experience of 2+ years in content creation in Mathematics, Statistics, Data Science, and Mac... Read Full Bio
Top Picks & New Arrivals
