Top-N and Third-Highest Salary Queries in PostgreSQL, MySQL, and Oracle

Define the ranking rule first

Suppose an employee table has employee_id, department_id, and salary. “Third highest salary” commonly means the third distinct non-null salary, not the third employee row. If salaries are 120, 100, 100, 80, the third distinct salary is 80; the third row after sorting may be 100.

Use DENSE_RANK when ties share a rank and ranks advance without gaps:

WITH ranked_salaries AS (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM employee
  WHERE salary IS NOT NULL
)
SELECT DISTINCT salary
FROM ranked_salaries
WHERE salary_rank = 3;

This syntax works in PostgreSQL, MySQL 8+, and Oracle versions with analytic/window functions. To find the third distinct salary within one department, filter the department inside the CTE before ranking so other departments do not affect the rank.

Return the employee rows at that salary

If the desired output is every employee who earns the third distinct salary, retain the key and return all rows at rank 3:

WITH ranked AS (
  SELECT employee_id,
         department_id,
         salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM employee
  WHERE salary IS NOT NULL
)
SELECT employee_id, department_id, salary
FROM ranked
WHERE salary_rank = 3
ORDER BY employee_id;

If the requirement is exactly one third row, use ROW_NUMBER() with a deterministic tie-breaker such as ORDER BY salary DESC, employee_id. State which rule is intended rather than allowing arbitrary tie behavior.

Dialect-specific top-N syntax

PostgreSQL: ORDER BY salary DESC LIMIT 3 returns up to three rows; use FETCH FIRST 3 ROWS ONLY for standard-style syntax. Use WITH TIES when the engine/version and requirement call for including peers at the boundary, or use a ranking function for explicit distinct-rank logic.

MySQL 8+: ORDER BY salary DESC LIMIT 3 returns three rows. Window functions are available for distinct ranking. Older MySQL versions without window functions can use nested DISTINCT selection, though ranking and per-group queries become less clear.

Oracle: modern Oracle supports FETCH FIRST 3 ROWS ONLY; analytic functions such as DENSE_RANK work for tie-aware ranking. Older Oracle queries often use an ordered inline view with ROWNUM, where applying ROWNUM before sorting gives the wrong result. With legacy syntax, sort in the inner query and limit in the outer query.

Correctness and performance checks

Decide whether null salaries are excluded, how ties are handled, whether ranking is global or per department, and what columns are returned. Test with duplicates, fewer than three distinct values, nulls, and departments with no rows. Then inspect the plan using EXPLAIN in PostgreSQL/MySQL or DBMS_XPLAN in Oracle. An index can help ordering or filtering, but its value depends on selectivity and table size.

The general pattern is portable: define the ranking semantics, use a window function for clarity, and adapt only the final-row limiting syntax to the target database.