SQL Query to Find Second Highest Salary

Finding the second-highest salary in SQL is a common requirement in data analysis and SQL interviews. This query is important for applications where tracking employee ranks, calculating percentile based bonuses, or analysing hierarchical data is necessary. Understanding how to retrieve the second-highest salary can be essential for data insights and optimizing queries.

Understanding the Second-Highest Salary Query

The second-highest salary query is used to find the employee who ranks second among salaries in a dataset. It’s a common task for business analysts, HR departments, and database administrators. This query is a subset of ranking and filtering operations and can be extended to find the Nth highest salary.

Real World Use Cases:

• Ranking Employees: Help HR determine employee rankings based on salary.
• Data Analysis: Analyse salary distribution trends.
• Reward Calculations: Assign percentile-based rewards based on salary ranks.

Methods to Find the Second-Highest Salary in SQL

Given an employee table with the following columns: name and salary, the goal is to find the employee(s) with the second-highest salary. Below is an example of such a dataset:

Employee Table

1. Using Subqueries

A subquery can be used to exclude the maximum salary and find the second-highest salary. Below is a simple query to find the employee whose salary is the highest and exclude it to get the second-highest salary.

Query:

SELECT name, salary  
FROM employee  
WHERE salary = (  
    SELECT MAX(salary)  
    FROM employee  
    WHERE salary < (SELECT MAX(salary) FROM employee)  
);

Output

Note: If you're using MySQL, be aware that we may encounter ERROR 1140 when running this query. This happens because the name column is unaggregated while using MAX(salary)

2. Using LIMIT ( MySQL -Specific)

In MSQL, the LIMIT clause is often used to retrieve the desired row based on rank. We can nest the previous query to find the second-highest salary. 

Query:

select *from employee 
group by salary 
order by  salary desc limit 1,1;

Output

Note: The LIMIT 1,1 clause is used to fetch one row starting from the second row in a descending order.

3. Using Common Table Expressions (CTEs) with Ranking Functions

In SQL Server and other databases that support ranking functions, such as DENSE_RANK(), we can make the process of finding the second-highest salary much simpler and more flexible.

Query:

WITH RankedSalaries AS (  
    SELECT name, salary,  
           DENSE_RANK() OVER (ORDER BY salary DESC) AS Rank  
    FROM employee  
)  
SELECT name, salary  
FROM RankedSalaries  
WHERE Rank = 2;  

Output:

Note : Why Use Ranking Functions? Using DENSE_RANK() ensures that the query accounts for ties in salaries. If two employees have the same highest salary, the next row will be ranked as the second-highest salary.

4. Finding the Nth-Highest Salary

To generalize the query for the Nth highest salary, instead of nesting for the second, third, etc largest salary, we can find the nth salary using a general query like in MySQL:

MySQL Query:

SELECT salary 
FROM employee 
ORDER BY salary desc 
limit n-1,1

Generic Query:

SELECT name, salary
FROM employee A
WHERE n-1 = (SELECT count(1) 
             FROM employee B 
             WHERE B.salary>A.salary)

5. Handling Duplicates in Salary

What if multiple employees have the same salary and you still need to find the second-highest salary? In this case, you can use a DISTINCT clause to ensure you only work with unique salary values.

Query for the 4th highest salary 

SELECT * FROM employee 
WHERE salary= (SELECT DISTINCT(salary) 
FROM employee ORDER BY salary DESC LIMIT 3,1);

General Query for Nth Highest Salary

SELECT * FROM employee 
WHERE salary= (SELECT DISTINCT(salary) 
FROM employee ORDER BY salary DESC LIMIT n-1,1);

Explanation: This query finds the 4th highest salary by selecting distinct salary values and using LIMIT to skip the first three unique salaries

6. Troubleshooting Common Errors (ERROR 1140)

While querying the database to fetch an employee with maximum salary, we might get ERROR 1140, specifically when GROUP BY is not used correctly with aggregated queries.

Error Message:

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column...  

We can clearly understand from the error message that our query is an aggregation query from 'MAX(salary)' and at the same time, it uses an unaggregated column 'name', which creates ambiguity for MySQL. This error will be captured by those versions of MySQL which contain the value 'only_full_group_by' in the 'sql_mode' variable. We can check this variable in MySQL using the following command.

SHOW VARIABLES LIKE "sql_mode";

Solution for ERROR 1140:

To avoid confusion, we must avoid using the aggregated column and the unaggregated column in the same query to eliminate this error. The following command would help in this.

// EMPLOYEE WITH HIGHEST SALARY
SELECT name, salary FROM employee ORDER BY salary DESC LIMIT 1; 

// EMPLOYEE WITH SECOND HIGHEST SALARY
SELECT name, salary FROM employee WHERE salary < (SELECT MAX(salary) FROM employee) ORDER BY salary DESC LIMIT 1;

// EMPLOYEE WITH Nth HIGHEST SALARY
SELECT name, salary FROM employee ORDER BY salary DESC LIMIT (N-1), 1;

Conclusion

This guide explained multiple ways to find the second-highest salary in SQL using subqueries, CTEs, and LIMIT functions. These techniques are essential for solving real-world database problems and acing SQL interview questions. Whether ranking employees or analyzing salary trends, mastering these queries enhances your data analysis skills and prepares you for handling complex datasets.