Third highest salary in every department

Question

Compensation benchmarking needs the third highest **distinct** salary inside each department.

From `payroll_records`, return `department` and `third_highest_salary`. Departments with fewer than three distinct salaries must be omitted entirely.

Accepted Answer

Step 1: The answer has one row per department, so the outer query walks the departments and the heavy lifting happens in correlated subqueries. Step 2: The inner scalar subquery selects `DISTINCT salary` for the matching department, sorts descending, then skips two rows (`OFFSET 2`) and takes one (`LIMIT 1`) — that is the third highest distinct salary. Step 3: Correlation happens through `p2.department = p.department`, which makes the subquery re-evaluate per department. Step 4: A second correlated subquery counts distinct salaries so departments with fewer than three (Marketing, Support) are filtered out instead of returning `NULL`. Step 5: `SELECT DISTINCT` on the outer query collapses the repeated employee rows down to one row per department.

Third highest salary in every department

More Subqueries questions

Asked at Oracle