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 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.

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

Create a table

Insert values in a table

Retrieve the top 1 row of each group

Try it out

Code explanation