How to Fetch Maximum Value for Each Group Using SQL

1. Introduction

Fetching the maximum value for each group is a common requirement in SQL when dealing with grouped data. For instance, we might want to find the highest-paid employee in each department or the latest order for each customer. Traditionally, this involves using subqueries, joins, or window functions.

In this tutorial, we’ll delve into various techniques for fetching the maximum value for each group using SQL, focusing on PostgreSQL, MySQL, and SQL Server. We’ll illustrate each method with practical examples using a suitable table we’ve created for this use case.

2. Understanding the Aim

Essentially, our goal is to find the row with the highest value for a specific column in each data group. To do this, the table needs to have one or more columns that separate the data into different groups.

So, this way, each group can be evaluated independently, ensuring that the row with the maximum value is correctly identified within its respective group.

Let’s observe the queries for creating the schema used in this tutorial:

CREATE TABLE Employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO Employees (name, department, salary) VALUES
('Alice', 'HR', 60000),
('Bob', 'HR', 80000),
('Charlie', 'IT', 90000),
('Dave', 'IT', 85000),
('Eve', 'Finance', 75000),
('Frank', 'Finance', 70000),
('Sam', 'IT', 98000),
('Tom', 'IT', 92000),
('Ursula', 'Finance', 83000),
('Vince', 'Finance', 79000),
('Wade', 'HR', 70000),
('Xena', 'HR', 88000);

As shown above, the query creates a table named Employees with four different columns: id, name, department, and salary. In this case, it’s the department column that groups employees into various departments.

3. Techniques for Fetching Maximum Value for Each Group

Now, let’s explore the techniques that can be used to extract the maximum value for each group in SQL. So, subqueries, common table expressions, and window functions are the techniques we’ll focus on in this tutorial.

3.1. Using Subqueries

Subqueries offer a straightforward approach to fetch the maximum value for each group within a table.

Let’s apply this technique to our schema and extract the rows with the highest value for salary within each department:

SELECT id, name, department, salary
FROM Employees e1
WHERE salary = (
    SELECT MAX(salary)
    FROM Employees e2
    WHERE e1.department = e2.department
);
 id |  name  | department |  salary
----+--------+------------+----------
  7 | Sam    | IT         | 98000.00
  9 | Ursula | Finance    | 83000.00
 12 | Xena   | HR         | 88000.00
(3 rows)

From the output, we can observe that there are three distinct departments. Each department is represented by a single row in the table, showing the highest salary recorded within each department.

3.2. Using Common Table Expressions

Common Table Expressions (CTEs) offer a more modular and readable approach for fetching the maximum value for each group within a table. Additionally, this approach enhances query maintainability.

By using a CTE, we first calculate the maximum value for each group using the MAX() function. Subsequently, we then join this result with the original table to extract the desired rows.

WITH MaxSalaries AS (
    SELECT department, MAX(salary) AS max_salary
    FROM Employees
    GROUP BY department
)
SELECT e.id, e.name, e.department, e.salary
FROM Employees e
JOIN MaxSalaries ms ON e.department = ms.department AND e.salary = ms.max_salary;
 id |  name  | department |  salary
----+--------+------------+----------
  7 | Sam    | IT         | 98000.00
  9 | Ursula | Finance    | 83000.00
 12 | Xena   | HR         | 88000.00
(3 rows)

As shown above, this result matches the previous one. Each department appears in a single row showing the highest salary recorded within that department.

3.3. Using Window Functions

Window functions provide a powerful and efficient method for fetching the maximum value for each group within a table. Specifically, this technique avoids the need for joins by leveraging the analytic capabilities of window functions.

The technique uses the ROW_NUMBER() function to effectively find the maximum salary value within each department in our dataset.

Here’s how to use a window function to achieve this:

SELECT id, name, department, salary
FROM (
    SELECT id, name, department, salary,
           ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM Employees
) sub
WHERE rn = 1;
 id |  name  | department |  salary
----+--------+------------+----------
  7 | Sam    | IT         | 98000.00
  9 | Ursula | Finance    | 83000.00
 12 | Xena   | HR         | 88000.00
(3 rows)

To begin with, the inner query calculates the row number for each row within every department, ordered by salary in descending order. In particular, the ROW_NUMBER() function assigns 1 to the row with the highest salary in each department, and the result of the subquery is referred to as sub.

Then, the outer query uses SELECT to specify columns from the sub result set where the new column rn is 1, indicating the highest salary for each department.

The outcome is identical to the other result sets shown for the previous two methods.

4. Conclusion

In this article, we explored various techniques for fetching the maximum value for each group using SQL. In particular, we discussed subqueries, CTEs, and window functions. Moreover, we illustrated each method with practical examples using a sample database schema.

By understanding these techniques, we can simplify complex queries and efficiently retrieve grouped data with maximum values.