Top Two Earners Per Department
HardSQLDatabaseSorting
Description
Write a SQL query to return the department, name, and salary of the two highest-paid employees in each department. Order by department ascending, then salary descending, then name ascending. **Output columns (in order):** department, name, salary
Table:
employees| Column | Type |
|---|---|
| id | INT |
| name | TEXT |
| salary | INT |
| department | TEXT |
Examples
Input:
CREATE TABLE employees (id INT, name TEXT, salary INT, department TEXT); INSERT INTO employees VALUES (1,'Ann',100,'A'),(2,'Ben',200,'A'),(3,'Cal',150,'A'),(4,'Dot',90,'B'),(5,'Eve',95,'B');Output:
A|Ben|200
A|Cal|150
B|Eve|95
B|Dot|90Explanation:
Within each department an employee is kept only when fewer than two colleagues outrank them by pay, giving the top two per group.
Input:
CREATE TABLE employees (id INT, name TEXT, salary INT, department TEXT); INSERT INTO employees VALUES (1,'Solo',50,'X');Output:
X|Solo|50Explanation:
Within each department an employee is kept only when fewer than two colleagues outrank them by pay, giving the top two per group.
Input:
CREATE TABLE employees (id INT, name TEXT, salary INT, department TEXT); INSERT INTO employees VALUES (1,'A',10,'D'),(2,'B',20,'D');Output:
D|B|20
D|A|10Explanation:
Within each department an employee is kept only when fewer than two colleagues outrank them by pay, giving the top two per group.
Constraints
- •
Use standard SQL (SQLite).