Sql Query To Get The Second Highest Salary

SQL Query to Get the Second Highest Salary

Finding the second highest salary is a classic interview question that tests a candidate’s ability to think beyond simple MAX() aggregation. While the problem sounds straightforward, real‑world data often contains duplicate salaries, NULL values, and varying database dialects, which makes crafting a dependable solution essential. This guide walks through multiple approaches, explains when each works best, and highlights performance considerations so you can choose the right technique for your environment But it adds up..

Why the Second Highest Salary Matters

Business analysts frequently need to identify salary bands, benchmark compensation, or detect outliers. The second highest salary helps answer questions such as:

• “What is the runner‑up top earner in each department?”
• “How does the second‑best paid employee compare to the median?”
• “Are there any gaps worth investigating for salary equity?”

Because the requirement appears in many technical interviews, mastering the different SQL patterns also strengthens your overall query‑writing skill set.

Sample Data Setup

Before diving into the queries, let’s define a simple employees table that we will reuse throughout the article.

CREATE TABLE employees (
    emp_id   INT PRIMARY KEY,
    emp_name VARCHAR(50),
    salary   DECIMAL(10,2),
    dept_id  INT
);

INSERT INTO employees (emp_id, emp_name, salary, dept_id) VALUES
(1, 'Alice',   90000.Day to day, 00, 10),
(2, 'Bob',     80000. And 00, 10),
(3, 'Charlie', 80000. 00, 20),
(4, 'Diana',   75000.00, 10),
(5, 'Eve',     70000.In real terms, 00, 20),
(6, 'Frank',   60000. 00, 30),
(7, 'Grace',   NULL,     20),
(8, 'Heidi',   90000.

Notice the presence of duplicate highest salaries (Alice and Heidi both earn 90 000) and a `NULL` salary. These edge cases will illustrate why some naïve solutions fail.

---

## Core Concept: What Does “Second Highest” Mean?  

When duplicates exist, there are two common interpretations:

1. **Distinct salary values** – ignore duplicates and return the second *different* salary.  
   In our data, the distinct salaries sorted descending are: 90 000, 80 000, 75 000, 70 000, 60 000. The second highest distinct salary is **80 000**.

2. **Row‑based ranking** – treat each row individually, so if the top salary appears multiple times, the second *row* may still be the same value.  
   Using `ROW_NUMBER()` or `RANK()` without `DISTINCT` would still return 90 000 for the second row because the first two rows share the same value.

Most interview questions expect the **distinct** interpretation unless explicitly stated otherwise. The solutions below cover both perspectives.

---

## Approach 1: Subquery with `ORDER BY … LIMIT` (MySQL, PostgreSQL, SQLite)  

The most readable method for databases that support `LIMIT`/`OFFSET` is to order salaries descending, skip the highest distinct value, and take the next one.

```sql
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Explanation

1. DISTINCT salary removes duplicate pay levels.
2. ORDER BY salary DESC sorts from highest to lowest.
3. OFFSET 1 skips the first (highest) distinct salary.
4. LIMIT 1 returns the next row – the second highest distinct salary.

Handling NULLs
If the column contains NULL, the query above will treat NULL as the lowest value (because NULL sorts last in ascending order and first in descending order depending on the DB). To exclude NULLs explicitly, add a WHERE salary IS NOT NULL clause:

SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Performance Note
The database must sort the distinct salary set, which can be expensive on large tables without an index on salary. Creating an index helps:

CREATE INDEX idx_employees_salary ON employees(salary);

Approach 2: Using OFFSET … FETCH (SQL Server, Oracle 12c+, PostgreSQL)

ANSI‑SQL introduced OFFSET … FETCH as a standard alternative to vendor‑specific LIMIT. The logic is identical.

SELECT DISTINCT salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY;

Approach 3: Subquery with MAX() and < (Works Everywhere)

A portable solution avoids LIMIT/OFFSET entirely by selecting the maximum salary that is less than the overall maximum.

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)
  AND salary IS NOT NULL;

Why it works

• The inner query finds the highest salary.
• The outer query looks for the greatest salary that is still lower than that value, effectively the second highest distinct salary.
• WHERE salary IS NOT NULL guarantees that NULL does not interfere with the comparison.

Duplicate‑aware variant (if you need the second row regardless of duplicates):

SELECT salary AS second_highest_salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
    WHERE salary IS NOT NULL
) ranked
WHERE rnk = 2;

DENSE_RANK() assigns the same rank to equal salaries, so the second distinct rank still yields 80 000 in our example.

Approach 4: Using NOT IN or NOT EXISTS

Another classic pattern