Find Second Highest Salary In Sql

Finding the second highest salary in SQL is one of the most classic interview questions for database developers and data analysts. Day to day, while the requirement sounds simple, the solution reveals a surprising depth regarding how different database engines handle sorting, distinct values, and window functions. Mastering this query is not just about memorizing syntax; it is about understanding the underlying mechanics of data retrieval, handling duplicates, and optimizing for performance across platforms like MySQL, PostgreSQL, SQL Server, and Oracle.

Understanding the Core Challenge

Before diving into syntax, it is crucial to define what "second highest" actually means in a business context. Consider a table where the top salaries are 100k, 100k, and 90k.

• Scenario A (Distinct Salary): The highest is 100k. The second highest distinct salary is 90k.
• Scenario B (Dense Rank): The highest is 100k (Rank 1). The next row is also 100k (Rank 1). The next distinct value is 90k (Rank 2).
• Scenario C (Row Number): The first row is 100k (Row 1). The second row is 100k (Row 2). The third row is 90k (Row 3).

Most interviewers and real-world reporting requirements imply Scenario A: finding the second highest distinct salary value. If the requirement is to find the employee who earns the second highest amount (handling ties specifically), the approach changes slightly. For this guide, we will focus primarily on the distinct value approach, as it is the standard interpretation, while noting how to handle ties.

Approach 1: The Correlated Subquery (Universal Compatibility)

This is the "old school" method taught in almost every academic curriculum. It works on virtually every relational database management system (RDBMS) ever made, from legacy Oracle 8i to modern cloud data warehouses.

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee);

How It Works

1. The inner subquery (SELECT MAX(Salary) FROM Employee) finds the absolute highest salary (e.g., 100,000).
2. The outer query filters the table WHERE Salary < 100000.
3. The outer MAX(Salary) then finds the maximum value remaining in that filtered set (e.g., 90,000).

Pros and Cons

• Pros: Extremely portable; runs on SQLite, Access, MySQL, SQL Server, PostgreSQL, Oracle without modification. Easy to explain to non-technical stakeholders.
• Cons: Performance can degrade on massive tables without proper indexing because the subquery executes conceptually for every row evaluation (though optimizers are smart enough to cache the max value usually). It returns NULL if there is no second highest salary (e.g., only one employee exists), which is correct behavior.

Approach 2: LIMIT / OFFSET (MySQL, PostgreSQL, SQLite)

Modern open-source databases introduced the LIMIT (or FETCH FIRST) clause, making pagination and "Top-N" queries trivial. This is often the preferred syntax for developers working in these ecosystems due to its readability But it adds up..

MySQL / PostgreSQL / SQLite Syntax

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

Breakdown

1. SELECT DISTINCT Salary: Removes duplicate salary values. This is the critical step. Without DISTINCT, if the top two earners make 100k, OFFSET 1 would return 100k again (the second row), not the second highest value.
2. ORDER BY Salary DESC: Sorts salaries from highest to lowest.
3. LIMIT 1 OFFSET 1: Skips the first row (the highest) and takes the very next row (the second highest).

Handling the "No Result" Edge Case

If the table has fewer than 2 distinct salaries, this query returns an empty result set, not NULL. If your application layer expects a scalar value (like a dashboard KPI), you should wrap it:

SELECT IFNULL((
    SELECT DISTINCT Salary
    FROM Employee
    ORDER BY Salary DESC
    LIMIT 1 OFFSET 1
), NULL) AS SecondHighestSalary;

(Note: Use COALESCE in PostgreSQL/SQLite instead of IFNULL.)

Approach 3: Window Functions (The Modern Standard)

If you are using SQL Server (2012+), PostgreSQL (8.Day to day, 0+), or Snowflake/BigQuery/Redshift, Window Functions are the professional standard. That said, 4+), Oracle (10g+), MySQL (8. They offer the most control over ranking logic (handling ties) and generally provide better execution plans for complex analytical queries Not complicated — just consistent..

Not obvious, but once you see it — you'll see it everywhere And that's really what it comes down to..

Using DENSE_RANK() (Recommended for Distinct Values)

DENSE_RANK assigns the same rank to ties but does not skip the next rank number. This perfectly maps to "Second Highest Distinct Salary."

WITH RankedSalaries AS (
    SELECT 
        Salary,
        DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
    FROM Employee
)
SELECT DISTINCT Salary AS SecondHighestSalary
FROM RankedSalaries
WHERE SalaryRank = 2;

Using ROW_NUMBER() (If you want the 2nd Row)

If the business rule dictates "Give me the salary of the person ranked #2 on the leaderboard" (where ties for #1 push the next person to #3), use ROW_NUMBER.

WITH RankedSalaries AS (
    SELECT 
        Salary,
        ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
    FROM Employee
)
SELECT Salary
FROM RankedSalaries
WHERE RowNum = 2;

Using RANK() (Standard Competition Ranking)

RANK skips numbers after a tie. (1, 1, 3). If you filter WHERE Rank = 2, you get no rows if there is a tie for first place. This is rarely what you want for "Second Highest Salary" but useful for "Who is the runner-up?" scenarios where ties for gold mean no silver medal.

Why Window Functions Win

1. Single Pass: The database scans the table once, sorts it (or uses an index), and computes ranks in memory.
2. Extensibility: You can easily select the Employee Name, Department, and Salary together in the final select without joining back to the base table.
3. Standards Compliant: This is ANSI SQL:2003 standard syntax.

Approach 4: Vendor-Specific Syntactic Sugar

SQL Server (TOP / OFFSET-FETCH)

Modern SQL Server supports the standard OFFSET ... FETCH syntax (since 2012), but the legacy TOP keyword is still widely seen.

-- Modern Standard (SQL Server 2012+)
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

-- Legacy Style (Still works)
SELECT TOP 1 Salary
FROM (
    SELECT DISTINCT TOP 2 Salary
    FROM Employee
    ORDER BY Salary DESC
) AS Sub
ORDER BY Salary ASC;

The legacy nested TOP trick works by grabbing the top

The legacy nested TOP trick works by grabbing the top two salaries in descending order and then selecting the one that comes immediately after the highest value. While functional, this method requires multiple steps and can be error-prone—especially when dealing with edge cases like ties or empty result sets. It also obscures the underlying ranking logic, making the query harder to maintain and optimize across different database platforms That's the whole idea..

In contrast, the DENSE_RANK() approach is generally the most dependable choice for this specific problem because it directly expresses intent: we want the second distinct value in an ordered set, regardless of how many ties occur before it. This makes the code self-documenting and less prone to subtle bugs during future maintenance.

Honestly, this part trips people up more than it should.

For teams working across multiple data warehouses, adopting DENSE_RANK() as the default pattern ensures consistency. If your team relies heavily on legacy systems with older versions of MySQL (pre-8.Most modern cloud databases (Snowflake, BigQuery, Redshift, PostgreSQL) implement these standard window functions efficiently, often using internal hash-based partitioning to keep memory usage low even on large tables. 0) or SQL Server (pre-2012), consider wrapping the window function logic in a stored procedure or view to abstract away platform differences Worth keeping that in mind. Took long enough..

Worth pausing on this one.

At the end of the day, while both ROW_NUMBER() and RANK() have their place in analytics, DENSE_RANK() remains the go-to solution for "second highest distinct salary" because it aligns perfectly with the semantic requirement: we care about distinct values, not arbitrary positional numbering. By leveraging built-in window functions, you eliminate the need for complex self-joins or manual offset calculations, resulting in cleaner, faster, and more portable queries. Whether you're building real-time dashboards or batch reports, this approach scales gracefully with data volume and keeps your codebase aligned with industry best practices.