Two employees tie for top salary; second highest via ORDER BY salary DESC LIMIT 1 OFFSET 1?

The 30 second answer

You get the other top-tied row again, not the second-highest distinct salary. Use distinct salaries with limit and offset, or a subquery for the max below the overall max, or dense rank equals two over salary descending, which also returns every employee at that salary. If there is no second distinct value, the result is empty or null.

Returns the top salary again (the second tied row), not the second-highest distinct value. Fix: SELECT DISTINCT salary … ORDER BY salary DESC LIMIT 1 OFFSET 1, or SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp), or DENSE_RANK() OVER (ORDER BY salary DESC) = 2 (also returns all employees with that salary). Edge: no second value → empty/NULL.

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

SELECT MAX(salary) FROM emp
 WHERE salary < (SELECT MAX(salary) FROM emp);

SELECT * FROM (
  SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM emp
) t WHERE rnk = 2;

Sources:

Sources (0)

See inline citations in the answer above.