How to Find the Second Highest Salary in SQL

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.

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:

1. Create a table.
2. Insert values into the table using INSERT INTO statement.
3. 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
( 
Emp_ID INTEGER, 
Emp_NAME VARCHAR(20), 
SALARY INTEGER 
)

2. Inserting Values: We will insert the values (records) in the above-created table schema using INSERT INTO clause.

Query:

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);

Output:

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);

Output:

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);

Output: 

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.

Using ROW_NUMBER() 

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. 

Query:

SELECT Salary AS SecondHighestSalary
FROM (
    SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM Employee
) AS RankedSalaries
WHERE SalaryRank = 2;

Output:

In the above query, you can also find the Nth highest salaries in SQL by changing the WHERE Clause with different numbers.

Using Correlated Subquery

The correlated subquery helps identify the second-highest salary by comparing and filtering salaries in the Employee table.

Query: 

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee E1
WHERE Salary < (
    SELECT MAX(Salary)
    FROM Employee E2
    WHERE E1.Salary < E2.Salary
)

Output: 

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.

Query:

SELECT Emp_NAME, SALARY
FROM (
    SELECT Emp_NAME, SALARY, RANK() OVER (ORDER BY SALARY DESC) AS salary_rank
    FROM Employee
) AS emp_rank
WHERE salary_rank = 2;

Output:

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. 

Query:

SELECT TOP 1 Salary AS SecondHighestSalary
FROM (
    SELECT DISTINCT TOP 2 Salary
    FROM Employee
    ORDER BY Salary DESC
) AS SecondHighestSalaries
ORDER BY Salary ASC;

Output:

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.

Query:

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.

Output:

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 Basic 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

Output: 

Conclusion

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.

FAQs