ROW_NUMBER() for Top N per Group

Problem

Each department head wants to know who their top earner is, but some salaries are identical, and they only want exactly ONE person per rank without duplicates. Assign a unique sequential row number to each employee within their respective department, ordered by salary descending. Return department_id, first_name, salary, and row_num.

Database Schema

Table: employees

• employee_id (INT): Unique identifier for the employee.
• first_name (VARCHAR): First name of the employee.
• last_name (VARCHAR): Last name of the employee.
• salary (INT): Employee's salary.
• department_id (INT): Foreign key referencing the department.
• manager_id (INT): Foreign key referencing the manager (another employee).
• hire_date (DATE): Date the employee was hired.

Table: departments

• department_id (INT): Unique identifier for the department.
• department_name (VARCHAR): Name of the department.

Try It

Hint

Solution