This blog covers various ways to find the second highest salary using SQL, breaking it down into simpler methods like the ROW_NUMBER(), correlated subquery, RANK() function, and the Top keyword. It also explains how to find higher ranks (the Nth-highest salaries) and what happens when multiple employees earn the same salary.
Table of Contents
Watch this video to learn more about SQL in detail:
How to Find the Second-Highest Salary in SQL
To find the second-highest salary, we have to follow certain steps:
- Create a table.
- Insert values into the table using INSERT INTO statement.
- Write the SQL query to find the second-highest salary.
Let us understand each step in detail and implement it to find the second-highest salary in SQL.
1. Creating a table: In this example, we will create an Employee table with their names and salaries.
Query to create a table
CREATE TABLE Employee
2. Inserting Values: We will insert the values (records) in the above-created table schema using INSERT INTO clause.
INSERT INTO Employee VALUES(1001,'Stephen',18000);
INSERT INTO Employee VALUES(1002,'Shreya',150000);
INSERT INTO Employee VALUES(1003,'Hardy',80000);
INSERT INTO Employee VALUES(1004,'Nobita',70000);
INSERT INTO Employee values(1005,'Poonam',11000);
INSERT INTO Employee VALUES(1006,'Amarpreet',35000);
3. Writing a Query: As we have already created a table, we will write and execute a query to find the second-highest salary in SQL. Here, we will use a normal query using the MAX function as MAX(salary) to find the highest salary and then find the second highest salary by nesting it.
Select *from Employee where salary=(select Max(salary) from employee);
We have calculated the maximum salary from the Employee table. Now, we will nest this query to find the second highest salary in SQL.
SELECT MAX(SALARY) As second_highest FROM Employee
WHERE SALARY < (SELECT MAX(SALARY) FROM Employee);
Enroll in this professional SQL Certification Course to learn more about databases and SQL from experts.
Ways to Calculate the Second-Highest Salary in SQL
There are several ways to calculate the second-highest salary in SQL, as the basic approach is already discussed above. Now, we will discuss other ways of calculating the second-highest salary in SQL using an example along with the execution of a query.
The ROW_NUMBER() in SQL assigns a rank to each salary in descending order (ORDER BY Salary DESC). Then, it selects the salary where the SalaryRank is 2, which corresponds to the second highest salary.
SELECT Salary AS SecondHighestSalary
SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS SalaryRank
) AS RankedSalaries
WHERE SalaryRank = 2;
In the above query, you can also find the Nth highest salaries in SQL by changing the WHERE Clause with different numbers.
The correlated subquery helps identify the second-highest salary by comparing and filtering salaries in the Employee table.
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee E1
WHERE Salary < (
FROM Employee E2
WHERE E1.Salary < E2.Salary
In the above query,
- The outer query (E1) looks for the maximum salary that is less than the maximum salary found in the inner query (E2).
- The inner query finds the maximum salary from the Employee table (E2), where it’s greater than the salary in the outer query (E1).
Using RANK() Function
The RANK() function in SQL assigns a ranking to rows based on specified criteria, like salary, in descending order. It helps identify the position of values, allowing retrieval of specific rankings, such as the second-highest salary.
SELECT Emp_NAME, SALARY
SELECT Emp_NAME, SALARY, RANK() OVER (ORDER BY SALARY DESC) AS salary_rank
) AS emp_rank
WHERE salary_rank = 2;
This query first assigns a rank to each salary in descending order using the RANK() function within a subquery. Then, it selects the employee name and salary where the rank is 2, which corresponds to the second highest salary.
Using Top Keyword
Using the Top keyword, we first select the top 2 distinct salaries in descending order from the Employee table. Then, it selects the top 1 from these 2 salaries in ascending order, which gives you the second highest salary.
SELECT TOP 1 Salary AS SecondHighestSalary
SELECT DISTINCT TOP 2 Salary
ORDER BY Salary DESC
) AS SecondHighestSalaries
ORDER BY Salary ASC;
Become a SQL Server expert with our comprehensive SQL Server tutorial!
How to Find the Nth-Highest Salaries in SQL
You can use the Top keyword to find the Nth highest salary in SQL Server. This is also faster than the previous solution because we calculate the Nth maximum salary without a subquery here.
SELECT DISTINCT SALARY
FROM Employee E1
WHERE N = (
SELECT COUNT(DISTINCT E2.SALARY)
FROM Employee E2
WHERE E2.SALARY >= E1.SALARY
Note: Replace N with the specific value representing the Nth-highest salary you want to find. If you want to find the 3rd highest salary, replace N in the query with 3, as the output is shown below.
In the above example, we have calculated the top 2 salaries from all the employees. In the same way, we can find any number of salaries in SQL.
Prepare yourself for the industry by going through these SQL Interview Questions now!
What if Multiple Employees Have the Same Salary?
In MS SQL, if the two salaries are the same in the dataset, it will show both records in the result.
Let us consider the following table where two employees have the same salary:
We will execute a query to check the output.
Select E1.* from Employee1 E1,Employee1 E2
where e1.SALARY=e2.SALARY and e1.Emp_ID!=e2.Emp_ID
In summary, finding the second-highest salary involves different methods, like the ROW_NUMBER(), correlated subquery, RANK() function, and the Top Keyword. These methods also help to handle situations with similar salaries and find higher ranks. Understanding these options gives you versatile tools to work with salary queries effectively in SQL, making it simpler to work in different scenarios.
Drop any of your queries in our SQL Server Community and start a discussion.
What if there are multiple employees with the same second-highest salary?
In such cases, these queries retrieve all employees with the second-highest salary. If multiple employees share the same salary, the query will display all those employees.
How can I adapt these queries to find the Nth-highest salary instead of the second-highest?
You can modify these queries by changing the WHERE clause to specify the desired rank. For instance, to find the third-highest salary, replace WHERE SalaryRank = 2 with WHERE SalaryRank = 3 in any of the queries mentioned above. Adjust this number to find the Nth-highest salary.
How do I optimize queries to find the second-highest salary for large databases?
Indexing the salary column, using appropriate database-specific optimizations, and minimizing the number of nested queries can enhance performance for large datasets when searching for the second-highest salary in SQL.
How can I handle scenarios where the second-highest salary does not exist?
If there is no distinct second-highest salary, queries return NULL or an empty result set. You can use COALESCE or IFNULL functions in SQL to handle such cases.