Forum

"How to Find the Se...
 
Share:
Notifications
Clear all

"How to Find the Second Highest Salary in SQL Without Using LIMIT or TOP"


Posts: 97
Admin
Topic starter
(@sql-admin)
Estimable Member
Joined: 5 years ago

Introduction
Retrieving the second highest salary in a table is a classic SQL challenge, often used in interviews. While many solutions use LIMIT or TOP, some SQL systems lack these features. In this post, we’ll explore a method to find the second highest salary without relying on these keywords, making the solution versatile across different SQL platforms.


Understanding the Problem
Let’s consider the following Employees table:

EmployeeID Name Salary
1 Alice 6000
2 Bob 8000
3 Charlie 7500
4 Diana 9000
5 Edward 8000

The task is to find the second highest salary, which is 8000 in this case.


SQL Query to Find the Second Highest Salary

sql
 
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

How the Query Works

  1. Find the Highest Salary:

    • The subquery (SELECT MAX(Salary) FROM Employees) retrieves the highest salary (9000).
  2. Exclude the Highest Salary:

    • The WHERE Salary < condition filters out the highest salary from the main query.
  3. Retrieve the Maximum of Remaining Salaries:

    • The outer query uses MAX(Salary) to find the second highest salary among the filtered results.

Expected Output

SecondHighestSalary
8000

Handling Ties in Salaries
If multiple employees share the same salary, this query will still correctly identify the second highest salary.

For example, if the salaries are 6000, 8000, 8000, 9000, the query will return 8000 as the second highest salary, even if it occurs multiple times.


Alternative Query Using RANK()
For systems that support window functions, you can use the RANK() function:

sql
 
SELECT Salary
FROM (
SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees
) RankedSalaries
WHERE Rank = 2;

This method directly ranks the salaries and retrieves the second highest value.


Applications of This Query

  1. Employee Analytics: Identify top-performing employees based on salary or sales.
  2. Data Audits: Spot inconsistencies or missing data in hierarchical values.
  3. Competitive Analysis: Compare salaries, scores, or other metrics in business datasets.

Conclusion
Finding the second highest salary in SQL is a fundamental skill with many practical applications. Whether you’re preparing for an interview or solving real-world data problems, this approach is a must-know. For more SQL queries and insights, check out our SQL forum.

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Share: