โ† Oracle questions
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 3

Confirm 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

  1. Confirm how ties should be handled (DENSE_RANK vs ROW_NUMBER) and schema details.

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.