We can retrieve the top 1 row of each group in SQL by using a subquery with a JOIN. We can do it by following the steps given below:
First, we will create a table. We make a table called employees
with the appropriate fields (department_id
, employee_id
, employee_name
, and salary
) with the help of the query given below:
CREATE TABLE employees (department_id INT,employee_id INT,employee_name VARCHAR(50),salary DECIMAL(10, 2),PRIMARY KEY (department_id, employee_id));
Now, we will insert data in the table created above. We insert data using the SQL statements given below:
-- Inserting data into the employees tableINSERT INTO employees (department_id, employee_id, employee_name, salary)VALUES(1001, 4001, 'Alice', 60000.00),(1001, 4002, 'Bob', 75000.00),(1002, 4003, 'Carol', 80000.00),(1002, 4004, 'David', 72000.00),(1003, 4005, 'Emily', 90000.00),(1003, 4006, 'Frank', 95000.00);
We consider the department a group according to the inserted data above. We can use the SQL statement below to get the top 1 (highest-paid) employee for each department:
SELECT e.department_id AS Dep_ID, e.employee_id AS Emp_ID, e.employee_name AS Name, e.salary AS SalaryFROM employees eJOIN (SELECT department_id, MAX(salary) AS max_salaryFROM employeesGROUP BY department_id) max_salariesON e.department_id = max_salaries.department_id AND e.salary = max_salaries.max_salary;
The output of the code given above can be observed below after clicking the “Run” button.
CREATE TABLE employees (department_id INT,employee_id INT,employee_name VARCHAR(50),salary DECIMAL(10, 2),PRIMARY KEY (department_id, employee_id));INSERT INTO employees (department_id, employee_id, employee_name, salary)VALUES(1001, 4001, 'Alice', 60000.00),(1001, 4002, 'Bob', 75000.00),(1002, 4003, 'Carol', 80000.00),(1002, 4004, 'David', 72000.00),(1003, 4005, 'Emily', 90000.00),(1003, 4006, 'Frank', 95000.00);SELECT e.department_id AS Dep_ID, e.employee_id AS Emp_ID, e.employee_name AS Name, e.salary AS SalaryFROM employees eJOIN (SELECT department_id, MAX(salary) AS max_salaryFROM employeesGROUP BY department_id) max_salariesON e.department_id = max_salaries.department_id AND e.salary = max_salaries.max_salary;
Lines 1–7: We create a table called employees
comprising four columns: department_id
as an integer for department identification, employee_id
as an integer for employee identification, employee_name
as a variable character column with a maximum 50-character capacity for holding employee names, and salary
as a decimal column with a precision of 10 digits and two decimal places for storing salaries. The table enforces data integrity by applying a PRIMARY KEY
constraint on the composite of department_id
and employee_id
, ensuring the uniqueness of department-employee pairs.
Lines 9–16: The provided code populates the employees
table by inserting data. Each VALUES
clause corresponds to a distinct set of values representing a single row to be added to the table.
Lines 18–25: The provided code retrieves data from the employees
table using the SELECT
statement to fetch specific columns, employing aliases to rename the result set columns. The main query involves joining the employees
table (e
) with a subquery (max_salaries
) based on department and salary. The subquery employs the MAX()
aggregate function to determine the maximum salary for each department, grouped accordingly. The output presents employees with the highest salary in their respective departments, utilizing aliases to rename columns: Dep_ID
for department_id
, Emp_ID
for employee_id
, Name
for employee_name
, and Salary
for salary
.
Free Resources