Forum

Forums
SQL
Interview Questions...
How to Write an SQL...
 
Share:
Notifications
Clear all

How to Write an SQL Query to Find the Second Highest Salary in an Employee Table?

 
    Last Post
RSS

Posts: 71
 Vinay Kumar
Guest
Topic starter
Nov 09, 2024 6:23 am
(@Vinay Kumar)
Trusted Member
Joined: 4 years ago

Write an SQL query to find the second-highest salary from an employee table. The table has columns such as employee_id, employee_name, and salary.

Answer:

Finding the second-highest salary is a common SQL interview question that can be solved using various techniques like LIMIT, DISTINCT, and DENSE_RANK. Let’s go through two efficient solutions, each using a different approach.

Solution 1: Using LIMIT with a Subquery

Assume the employee table structure is as follows:

employee_id employee_name salary
1 Alice 90000
2 Bob 85000
3 Charlie 75000
4 David 85000
5 Eve 60000

In this case, we want to retrieve the second-highest unique salary, ignoring any duplicate salary values.

SQL Query:

sql
 
SELECT MAX(salary) AS second_highest_salary
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Explanation:

  1. Subquery for MAX(salary): We first find the maximum salary using the subquery SELECT MAX(salary) FROM employee, which returns the highest salary in the table.
  2. Condition for Second Highest Salary: In the main query, we then use a WHERE clause to filter salaries less than this maximum, effectively narrowing down to the second-highest salary.
  3. MAX Function Again: Finally, MAX(salary) on this subset returns the second-highest salary.

This approach is efficient for tables with a large number of records and helps avoid duplicate values by naturally filtering out the highest salary.

Solution 2: Using the DENSE_RANK() Window Function

Another way to approach this is by using the DENSE_RANK() function. This method is particularly helpful if there are duplicates in the salary values and you want to find the unique ranks.

SQL Query:

sql
 
SELECT salary AS second_highest_salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employee
) AS ranked_salaries
WHERE rnk = 2;

Explanation:

  1. DENSE_RANK() Function: The DENSE_RANK() OVER (ORDER BY salary DESC) function ranks the salaries in descending order, assigning the same rank to duplicate salary values. This ensures we get the correct ranking even if multiple employees have the same salary.
  2. Subquery for Filtering: The subquery assigns ranks to each unique salary, allowing us to filter for the second-highest rank by adding a WHERE rnk = 2 condition in the main query.
  3. Final Output: This query will return the second-highest unique salary.

Expected Output:

If our table has salaries like 90000, 85000, 75000, and 60000, the second-highest salary (assuming no duplicates) would be:

second_highest_salary
85000

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: