SQL - Min & Max

SQL Aggregate Functions: MIN() and MAX()

Introduction to Aggregate Functions

Aggregate functions perform a calculation on a set of rows and return a single, summary value. They are essential for data analysis and reporting. The most common aggregate functions are:

• COUNT(): Counts the number of rows.
• SUM(): Calculates the sum of values in a column.
• AVG(): Calculates the average of values in a column.
• MAX(): Returns the largest value in a set.
• MIN(): Returns the smallest value in a set.

In this tutorial, we will focus on MAX() and MIN().

Setup: Sample Data

For our examples, let’s use an Employees table.

CREATE TABLE Employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    hire_date DATE,
    salary NUMERIC(10, 2)
);

INSERT INTO Employees (first_name, last_name, department, hire_date, salary) VALUES
('Alice', 'Smith', 'Engineering', '2020-03-15', 90000.00),
('Bob', 'Johnson', 'Engineering', '2019-07-22', 115000.00),
('Charlie', 'Brown', 'Marketing', '2021-01-10', 72000.00),
('Diana', 'Prince', 'Marketing', '2022-06-01', 80000.00),
('Ethan', 'Hunt', 'Operations', '2018-11-05', 105000.00);

The SQL MAX() Function

The MAX() function returns the largest value from a specified column.

Basic Usage

To find the highest salary among all employees:

SELECT MAX(salary) AS highest_salary
FROM Employees;

This query will return a single row and column with the value 115000.00.

MAX() with GROUP BY

MAX() becomes even more powerful when combined with the GROUP BY clause to find the maximum value within different groups. For example, to find the highest salary in each department:

SELECT department, MAX(salary) AS highest_salary_in_dept
FROM Employees
GROUP BY department;

This will produce:

department	highest_salary_in_dept
Engineering	115000.00
Marketing	80000.00
Operations	105000.00

MAX() in a Subquery or CTE

Often, you don’t just want the maximum value; you want the entire row associated with that value. A common way to achieve this is by using MAX() in a subquery.

SELECT first_name, last_name, salary
FROM Employees
WHERE salary = (SELECT MAX(salary) FROM Employees);

A more modern and often more readable approach for complex queries is to use a Common Table Expression (CTE).

WITH MaxSalary AS (
    SELECT MAX(salary) AS max_sal FROM Employees
)
SELECT e.first_name, e.last_name, e.salary
FROM Employees e, MaxSalary ms
WHERE e.salary = ms.max_sal;

The SQL MIN() Function

The MIN() function is the counterpart to MAX(), returning the smallest value from a specified column.

Basic Usage

To find the lowest salary among all employees:

SELECT MIN(salary) AS lowest_salary
FROM Employees;

Result: 72000.00

MIN() with Different Data Types

MIN() and MAX() work on more than just numbers. They can be used on strings (alphabetical order) and dates (chronological order).

Find the earliest hire date (the ‘minimum’ date):

SELECT MIN(hire_date) AS first_hire_date
FROM Employees;

Result: 2018-11-05

Find the first name in alphabetical order:

SELECT MIN(first_name) AS first_alphabetically
FROM Employees;

Result: Alice

Performance Considerations

For very large tables, finding the minimum or maximum value can require a full table scan, which can be slow. If you frequently query for the MIN() or MAX() of a specific column, creating an index on that column can dramatically improve performance. The database can then simply look at the first or last entry in the index instead of scanning every row.

Practical Examples

Let’s answer some business questions:

Question: Which department has the most recently hired employee?

SELECT department
FROM Employees
WHERE hire_date = (SELECT MAX(hire_date) FROM Employees);

Result: Marketing

Question: What is the salary range (minimum and maximum salary) in the Engineering department?

SELECT
    MIN(salary) AS min_eng_salary,
    MAX(salary) AS max_eng_salary
FROM Employees
WHERE department = 'Engineering';

Result: 90000.00, 115000.00