Second highest salary in each department in SQL, with ties

To find the second highest salary in each department in SQL, rank the salaries inside each department with DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) and keep the rows ranked 2. PARTITION BY restarts the ranking for every department, and DENSE_RANK gives tied salaries the same rank, so two people sharing the top salary do not push the real second salary out. A department with one distinct salary returns no row.

How do I find the second highest salary in each department?

DENSE_RANK() with PARTITION BY department finds the second highest salary in each department and every employee who earns it. The window function ranks salaries from the highest down, separately for each department, and the outer query keeps rank 2. Ava and Liam share the top Engineering salary, so Noah and Zoe at 7500 are the second highest:

WITH employees(name, department, salary) AS (
  VALUES ('Ava', 'Engineering', 9000), ('Liam', 'Engineering', 9000), ('Noah', 'Engineering', 7500),
         ('Zoe', 'Engineering', 7500), ('Eli', 'Engineering', 6000), ('Mia', 'Sales', 6000),
         ('Omar', 'Sales', 5200), ('Jade', 'Sales', 4800), ('Kai', 'Support', 4000)
),
ranked AS (
  SELECT name, department, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rnk = 2
ORDER BY department, name;

Use DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) and filter on rank 2 in an outer query. The filter cannot sit in the same query’s WHERE, because WHERE runs before window functions are computed. Support has only Kai, so it has no rank 2 and no row; a later section returns NULL for it instead.

When you need one row per department rather than every employee, select DISTINCT department, salary from the ranked rows.

Why does ROW_NUMBER return the wrong second highest salary per department?

ROW_NUMBER() numbers rows, not salaries. When two employees share the top salary in a department, they get 1 and 2, so row 2 holds the top salary again. Engineering answers 9000, which is its highest salary, not its second highest:

WITH employees(name, department, salary) AS (
  VALUES ('Ava', 'Engineering', 9000), ('Liam', 'Engineering', 9000), ('Noah', 'Engineering', 7500),
         ('Zoe', 'Engineering', 7500), ('Eli', 'Engineering', 6000), ('Mia', 'Sales', 6000),
         ('Omar', 'Sales', 5200), ('Jade', 'Sales', 4800), ('Kai', 'Support', 4000)
),
numbered AS (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, name) AS rn
  FROM employees
)
SELECT department, name, salary
FROM numbered
WHERE rn = 2
ORDER BY department;

Sales looks right only because its salaries are all different, which is why a query can pass on clean test data and fail the grader. RANK() fails a third way: after the tie at the top it skips to rank 3, so Engineering has no rank 2 at all. The RANK vs DENSE_RANK vs ROW_NUMBER post shows all three side by side.

Why does my query return only one department?

A ranking query returns only one department’s answer when the window has no PARTITION BY. DENSE_RANK() OVER (ORDER BY salary DESC) ranks the whole company at once, so rank 2 is the second highest salary overall, and it happens to be in Engineering:

WITH employees(name, department, salary) AS (
  VALUES ('Ava', 'Engineering', 9000), ('Liam', 'Engineering', 9000), ('Noah', 'Engineering', 7500),
         ('Zoe', 'Engineering', 7500), ('Eli', 'Engineering', 6000), ('Mia', 'Sales', 6000),
         ('Omar', 'Sales', 5200), ('Jade', 'Sales', 4800), ('Kai', 'Support', 4000)
),
ranked AS (
  SELECT name, department, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rnk = 2
ORDER BY department, name;

Add PARTITION BY department before the ORDER BY inside OVER (...). The company-wide version is the plain second highest salary question, which has its own pitfalls with LIMIT and OFFSET.

How do I find the second highest salary in each department without window functions?

Without window functions, take the largest salary in each department that is below that department’s maximum. A correlated subquery finds the maximum for the current row’s department, and GROUP BY returns one row per department. The query runs on MySQL 5.7, which has no window functions:

WITH employees(name, department, salary) AS (
  VALUES ('Ava', 'Engineering', 9000), ('Liam', 'Engineering', 9000), ('Noah', 'Engineering', 7500),
         ('Zoe', 'Engineering', 7500), ('Eli', 'Engineering', 6000), ('Mia', 'Sales', 6000),
         ('Omar', 'Sales', 5200), ('Jade', 'Sales', 4800), ('Kai', 'Support', 4000)
)
SELECT e.department, MAX(e.salary) AS second_highest
FROM employees AS e
WHERE e.salary < (
  SELECT MAX(m.salary)
  FROM employees AS m
  WHERE m.department = e.department
)
GROUP BY e.department
ORDER BY e.department;

Ties at the top cannot fool it, because salary < 9000 removes every 9000 at once. On a large table, an index on (department, salary) keeps the subquery fast. For the names of the employees at that salary, the window function query is shorter.

How do I show NULL for a department with no second highest salary?

Start from the list of departments and LEFT JOIN the rank 2 salaries to it. A department with one distinct salary, or no employees, then keeps its row with NULL in the salary column. Here the departments come from their own table, as they usually do, and Legal has nobody yet:

WITH departments(id, name) AS (
  VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'Support'), (4, 'Legal')
),
employees(name, dept_id, salary) AS (
  VALUES ('Ava', 1, 9000), ('Liam', 1, 9000), ('Noah', 1, 7500), ('Zoe', 1, 7500),
         ('Mia', 2, 6000), ('Omar', 2, 5200), ('Kai', 3, 4000)
),
second_salaries AS (
  SELECT DISTINCT dept_id, salary
  FROM (
    SELECT dept_id, salary,
           DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
    FROM employees
  ) AS ranked
  WHERE rnk = 2
)
SELECT d.name AS department, s.salary AS second_highest
FROM departments AS d
LEFT JOIN second_salaries AS s ON s.dept_id = d.id
ORDER BY d.id;

The DISTINCT matters: without it, Engineering appears twice, once for Noah and once for Zoe. Put any condition on the joined salaries inside second_salaries or the ON clause, not in the final WHERE, or the LEFT JOIN turns back into an inner join and the NULL rows disappear.

How do I find the Nth highest salary in each department?

Change the number in the rank filter. rnk = 3 gives the third highest salary in each department, and the same query with rnk <= 3 gives the top three salaries per department, ties included:

WITH employees(name, department, salary) AS (
  VALUES ('Ava', 'Engineering', 9000), ('Liam', 'Engineering', 9000), ('Noah', 'Engineering', 7500),
         ('Zoe', 'Engineering', 7500), ('Eli', 'Engineering', 6000), ('Mia', 'Sales', 6000),
         ('Omar', 'Sales', 5200), ('Jade', 'Sales', 4800), ('Kai', 'Support', 4000)
),
ranked AS (
  SELECT name, department, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rnk = 3
ORDER BY department, name;

If the salary column allows NULL, add WHERE salary IS NOT NULL inside the ranked query. PostgreSQL sorts NULL first in a descending order, so a NULL salary would take rank 1 in its department.

The PostgreSQL tutorial on window functions explains PARTITION BY, the PostgreSQL window functions reference defines dense_rank, and the MySQL 8.0 window functions chapter covers the same syntax there. More questions of the same shape are in the SQL interview practice guide.

Related questions

The employee with the highest salary in each department is the same query with rnk = 1, and it has a pitfall of its own: SELECT department, name, MAX(salary) ... GROUP BY department is an error in PostgreSQL. Top N per group, such as the top three salaries in each department, is a separate question when you need exactly N rows per group rather than N salaries.