How to find second highest salary in sql
Updated on 15th Feb, 24 9.1K Views

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:

  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:

Inserting values into the table

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:

output of second highest salary query

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: 

output of second highest salary query

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:

Output of calculating second highest salary Using ROW_NUMBER()

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: 

Using Correlated Subquery

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:

Using ranl function

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:

Using Top Keyword

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:

How to Find the Nth-Highest Salaries in SQL

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:

What if Multiple 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: 

Output Image for What if Multiple Employees Have the Same Salary?

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.


Drop any of your queries in our SQL Server Community and start a discussion.

FAQs

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.

Course Schedule

Name Date Details
SQL Training 02 Mar 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 09 Mar 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 16 Mar 2024(Sat-Sun) Weekend Batch
View Details

Leave a Reply

Your email address will not be published. Required fields are marked *

Speak to our course Advisor Now !

Related Articles

Associated Courses

Subscribe to our newsletter

Signup for our weekly newsletter to get the latest news, updates and amazing offers delivered directly in your inbox.