SQL Querying 101: How to Find Second Highest Salary in SQL
When working with SQL, finding the second-highest salary is a common task. It is essential when working with databases that contain employee information, payroll data, or sales data.
In this article, we will explore different ways to find second-highest salary in SQL.
Method 1: Using ORDER BY and LIMIT Clause
One way to find the second-highest salary is by using the ORDER BY and LIMIT clause. Here is the SQL query:
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;
This query retrieves all unique salaries from the “Employee” table, orders them in descending order, skips the first salary, and returns the second salary.
Method 2: Using Subqueries
Another way to find the second-highest salary is by using subqueries. Here is the SQL query:
SELECT MAX(Salary)
FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);
This query retrieves the maximum salary from the “Employee” table which is not equal to the maximum salary.
Method 3: Using the RANK() function
We can also use the RANK() function to find the second-highest salary. Here is the SQL query:
SELECT Salary
FROM (
SELECT Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employee
) AS t
WHERE Rank = 2;
This query assigns a rank to each salary in descending order using the RANK() function, then selects the salary with rank 2.
Conclusion
In conclusion, finding the second-highest salary in SQL is a common and essential task. We explored three different ways to find second-highest salary in SQL: using ORDER BY and LIMIT clause, using subqueries, and using the RANK() function. You can choose any method based on your requirements and database schema. With these techniques, you can efficiently retrieve the second-highest salary and perform further analysis on your data.