CodingReported in a candidate post, Aug 2024
SQL queries for Nth highest salary and department-wise highest salary
Filed under Oracle
Free read 1 of 3Confirm how ties should be handled (DENSE_RANK vs ROW_NUMBER) and schema details.
The question
Write SQL queries to retrieve the Nth highest salary globally from an employee table, and department-wise highest/Nth highest salaries using window functions.
Constraints
- Employee table contains columns like id, salary, and dept_id
- Must handle duplicate salaries correctly
Approach
Confirm how ties should be handled (DENSE_RANK vs ROW_NUMBER) and schema details.
Self-joins or subqueries with NOT IN clauses for finding Nth maximum values.
Use window functions like DENSE_RANK() OVER (ORDER BY salary DESC) wrapped in a Common Table Expression (CTE) to filter for specific ranks globally and partitioned by department.
Departments with no employees or fewer than N employees.
Tips
- What they are testing: Test with duplicate salaries, departments with fewer employees than N, and empty tables.
- Common mistake: Using LIMIT/OFFSET incorrectly when multiple employees share the exact same salary rank.
Related Oracle questions
Collected from public candidate posts and coding forums. Updated Nov 2026.