SQL query to find third highest salary in company

Mastering SQL: Finding the Nth Highest Salary in a Company

Discover various SQL techniques, from basic subqueries to advanced window functions, to efficiently retrieve the Nth highest salary from an employee table. This guide focuses on finding the third highest salary.

Finding the Nth highest salary is a common SQL interview question and a practical problem in database management. While it might seem straightforward, there are several approaches, each with its own advantages and performance characteristics. This article will walk you through different methods to find the third highest salary, which can be easily adapted to find any Nth highest salary.

Understanding the Challenge

The core challenge lies in correctly identifying and ranking salaries, especially when dealing with duplicate salary values. A simple ORDER BY and LIMIT might not always yield the correct result if multiple employees share the same salary. We need a robust method that considers distinct salaries for ranking.

flowchart TD
    A[Start] --> B{Retrieve All Salaries};
    B --> C{Remove Duplicates?};
    C -- Yes --> D[Get Distinct Salaries];
    C -- No --> B;
    D --> E{Order Salaries Descending};
    E --> F{Select Nth Value};
    F --> G[End];

General workflow for finding the Nth highest salary

Method 1: Using a Subquery with LIMIT/OFFSET

This is one of the most common and intuitive ways to find the Nth highest salary. It involves ordering the salaries in descending order, then using LIMIT and OFFSET (or ROWNUM in Oracle, TOP in SQL Server) to pick the desired rank. To handle duplicates correctly, we first select distinct salaries.

SELECT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 2;

SQL query using LIMIT and OFFSET to find the 3rd highest salary (MySQL/PostgreSQL)

Method 2: Using a Subquery with NOT IN / NOT EXISTS

Another approach involves finding salaries that are not among the top N-1 highest salaries. This method can be less efficient for very large N but provides a clear logical flow. We count how many distinct salaries are greater than the current salary. If this count is N-1, then the current salary is the Nth highest.

SELECT DISTINCT Salary
FROM Employee E1
WHERE 2 = (SELECT COUNT(DISTINCT Salary)
           FROM Employee E2
           WHERE E2.Salary > E1.Salary);

SQL query using a subquery with COUNT(DISTINCT) to find the 3rd highest salary

Method 3: Using Window Functions (DENSE_RANK)

Window functions, particularly DENSE_RANK(), offer a powerful and often more efficient way to solve this problem. DENSE_RANK() assigns a unique rank to each distinct salary, without gaps, even if there are ties. This is ideal for finding the Nth highest distinct value.

WITH RankedSalaries AS (
    SELECT
        Salary,
        DENSE_RANK() OVER (ORDER BY Salary DESC) as RankNum
    FROM
        Employee
)
SELECT Salary
FROM RankedSalaries
WHERE RankNum = 3;

SQL query using DENSE_RANK() window function to find the 3rd highest salary

Choosing the Right Method

The best method depends on your specific database system and performance requirements. For most modern databases, DENSE_RANK() is often the most efficient and readable solution. For older systems or simpler cases, LIMIT/OFFSET with DISTINCT is a good alternative. The correlated subquery method is generally less recommended for performance-critical scenarios.