postJune 4, 2026

Find the second highest salary per department — SQL

#sql#window-functions#interview

Medium⏱️ Logic Challenge

Given an "employees" table with columns (id, name, department, salary), return the second highest salary per department. If a department has fewer than 2 distinct salaries, exclude it.

Ver solución

WITH ranked AS (
  SELECT
    department,
    name,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rnk
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rnk = 2;

"Find the second highest X per group" appears in interviews because it tests whether you understand window functions versus simple aggregates. MAX() alone gives you the highest. To get the second, you need ranking.

The pattern: rank rows within each group by the column of interest using DENSE_RANK() OVER (PARTITION BY group ORDER BY column DESC), then filter to rank = 2. DENSE_RANK handles ties cleanly — two people tied for first both get rank 1, and the next person is rank 2.