Write an SQL query to find the second highest salary.

Imagine the marks of a class written on a board, and you want the second-best score. One way: find the top score, cross it out everywhere it appears, and the best of what remains is your answer. Another way: write each distinct score once, sort them from high to low, and pick the second one.

The trap is ties. If two people share the top salary of ₹90,000, the second highest salary is the next different amount, not ₹90,000 again. Every correct approach must handle that.

Table employees:

Expected answer: 75000.

Approach 1: subquery with MAX (works in every SQL database)

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

• The inner query finds 90000.
• The outer query looks only at salaries below 90000 and takes the largest: 75000.
• If there is no second salary, MAX of zero rows returns NULL, which is usually what the question wants.

Approach 2: DISTINCT with ORDER BY, LIMIT and OFFSET (PostgreSQL, MySQL, SQLite)

SELECT (
  SELECT DISTINCT salary
  FROM employees
  ORDER BY salary DESC
  LIMIT 1 OFFSET 1
) AS second_highest;

• DISTINCT removes the duplicate 90000, so the list is 90000, 75000, 60000.
• OFFSET 1 skips the first value, LIMIT 1 takes the next one.
• The outer SELECT ( ... ) makes the query return NULL instead of no rows when there is no second salary.
• For the Nth highest, use OFFSET N - 1. In SQL Server, write OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY instead.

Approach 3: DENSE_RANK window function (best for Nth highest and per group)

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

• DENSE_RANK gives 90000 rank 1 (both rows), 75000 rank 2, 60000 rank 3. No numbers are skipped after a tie.
• DISTINCT is needed because several employees can share rank 2.
• RANK would be wrong here if you ask for rank 2 after a tie: it would give 1, 1, 3, 4, so no row has rank 2.

Bonus: second highest salary in each department

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

PARTITION BY dept restarts the ranking for each department. Result: HR, 60000. IT has no second distinct salary, so it does not appear.

Extra space is O(n) in the worst case for sorting. Real databases may use an index to avoid the sort.

• ORDER BY salary DESC LIMIT 1 OFFSET 1 without DISTINCT. With two people on 90000, it returns 90000.
• Using ROW_NUMBER or RANK instead of DENSE_RANK, which breaks on ties.
• Returning no rows instead of NULL when the question (as on LeetCode) asks for NULL.
• Using TOP or LIMIT syntax that the interviewer's database does not support. Say which database you are writing for.

• How would you find the Nth highest salary without window functions?
• How would you return the names of all employees with the second highest salary?
• What is the difference between ROW_NUMBER, RANK and DENSE_RANK? See ROW_NUMBER vs RANK vs DENSE_RANK.