How to Find the Second Highest Salary in SQL Using DENSE_RANK()

This is Question is most use full in Sql interview ,Below is Employee Table and we are going to write Sql Query using Dense rank function that is windowing function :

Employee Table Example

Let’s consider an Employee table with the following data:

Our goal is to find the second highest salary in this table.

What is DENSE_RANK()?

DENSE_RANK() is a window function in SQL that assigns ranks to rows within a partition of a result set. Unlike RANK(), it does not skip ranks if there are duplicate values. This makes it ideal for scenarios like finding the second highest salary, where you want consecutive rankings.

SQL Query Using DENSE_RANK()

To retrieve the second highest salary in each department, we can use the DENSE_RANK() function. Here’s how:

Step 1: Assign Ranks Using DENSE_RANK()

SELECT Name, Age, Salary, Department,
       DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM Employee;
        

Step 2: Filter for the Second Highest Salary

To filter only the second highest salary, we use a Common Table Expression (CTE):

WITH RankedSalaries AS (
    SELECT Name, Age, Salary, Department,
           DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
    FROM Employee
)
SELECT Name, Age, Salary, Department
FROM RankedSalaries
WHERE Rank = 2;
        

Explanation of the Query

1. DENSE_RANK(): Assigns ranks to employees based on their salary in descending order.
2. PARTITION BY Department: Ensures ranking is done within each department.
3. CTE (WITH Clause): Stores the ranked result temporarily.
4. WHERE Rank = 2: Filters employees with the second highest salary.

Why Use DENSE_RANK() Instead of RANK()?

• RANK() skips ranks if there are duplicate salaries. For example, if two employees share the highest salary, the next rank would be 3, skipping rank 2.
• DENSE_RANK() ensures consecutive rankings, making it ideal for fetching the second highest salary.

Example Comparison:

Conclusion

Using DENSE_RANK() is an efficient and reliable way to find the second highest salary in an SQL table. It ensures consecutive rankings and works seamlessly with the PARTITION BY clause to fetch department-specific results.

Key Takeaways:

• DENSE_RANK() is ideal for ranking without gaps.
• Use PARTITION BY to rank salaries within each department.
• Combine DENSE_RANK() with a CTE for clean and efficient queries.

Looking for More SQL Tips?

Follow us on Instagram, LinkedIn, Facebook, and Twitter for regular updates on SQL, database management, and interview questions!

This solution is provided by Shubham Mishra and contributed by the Developer Indian team. If you have any queries or suggestions, feel free to leave a comment! 🚀