SQL Query to Find the Second Highest Salary: Easy Examples for Beginners

by

Method 1: Using LIMIT (MySQL, PostgreSQL)

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

Explanation:

  • ORDER BY salary DESC → sorts salaries in descending order.
  • OFFSET 1 → skips the first (highest) salary.
  • LIMIT 1 → fetches the next one (second highest).

Method 2: Using Subquery (Works in most databases)

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Explanation:

  • Inner query gets the highest salary.
  • Outer query finds the maximum salary less than the highest → which is the second highest.

Method 3: Using DENSE_RANK() (SQL Server, Oracle, PostgreSQL)

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked_salaries
WHERE rnk = 2;

Explanation:

  • DENSE_RANK() assigns a rank to each salary.
  • rnk = 1 → highest salary, rnk = 2 → second highest salary.

For interviews, Method 2 (Subquery) and Method 3 (Window Function) are the most commonly asked.