Description
Write a SQL query to find employees who have the highest salary in each department. Return department, employee name, and salary. **Output columns (in order):** department, name, salary
employees| Column | Type |
|---|---|
| id | INT |
| name | TEXT |
| salary | INT |
| department | TEXT |
Examples
CREATE TABLE employees (id INT, name TEXT, salary INT, department TEXT); INSERT INTO employees VALUES (1, 'Alice', 50000, 'Engineering'), (2, 'Bob', 60000, 'Marketing'), (3, 'Charlie', 55000, 'Engineering');Engineering|Charlie|55000
Marketing|Bob|60000In Engineering, Charlie (55000) earns more than Alice (50000). Bob is the only one in Marketing.
CREATE TABLE employees (id INT, name TEXT, salary INT, department TEXT); INSERT INTO employees VALUES (1, 'David', 75000, 'Sales'), (2, 'Emma', 80000, 'HR'), (3, 'Frank', 75000, 'Sales'), (4, 'Grace', 70000, 'Sales'), (5, 'Henry', 85000, 'Finance'), (6, 'Iris', 82000, 'Finance');Sales|David|75000
Sales|Frank|75000
HR|Emma|80000
Finance|Henry|85000This example demonstrates handling tied salaries within a department. Both David and Frank earn 75000 in Sales (the highest for that department), so both are returned. Each department's highest earner(s) are identified regardless of ties.
CREATE TABLE employees (id INT, name TEXT, salary INT, department TEXT); INSERT INTO employees VALUES (1, 'John', 95000, 'IT'), (2, 'Kate', 120000, 'Executive'), (3, 'Liam', 45000, 'Support');IT|John|95000
Executive|Kate|120000
Support|Liam|45000Each department (IT, Executive, Support) has exactly one employee, so John (95000), Kate (120000), and Liam (45000) are each the highest earner by default. The query must still correctly identify MAX(salary) per department even when there's no competition.
Constraints
- •
Use subquery or window function