How to get the top 1 row of each group in SQL

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:

Create a table

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)
);

Insert values in a table

Now, we will insert data in the table created above. We insert data using the SQL statements given below:

-- Inserting data into the employees table
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);

Retrieve the top 1 row of each group

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 Salary
FROM employees e
JOIN (
  SELECT department_id, MAX(salary) AS max_salary
  FROM employees
  GROUP BY department_id
) max_salaries
ON e.department_id = max_salaries.department_id AND e.salary = max_salaries.max_salary;

Try it out

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 Salary
FROM employees e
JOIN (
  SELECT department_id, MAX(salary) AS max_salary
  FROM employees
  GROUP BY department_id
) max_salaries
ON e.department_id = max_salaries.department_id AND e.salary = max_salaries.max_salary;

Code explanation

  • 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

Copyright ©2025 Educative, Inc. All rights reserved