Minimum and Maximum Values: MIN() and MAX()

Let’s dive into the world of extremes in data — finding minimum and maximum values. Don’t worry, SQL won’t make you learn anything radically new, but you’ll see how to grab the biggest and smallest values in your tables. These functions are everywhere: from analyzing movie ratings on IMDb to figuring out the lowest temperature on Mars.

MIN() and MAX() are aggregate functions that return the minimum and maximum values from a dataset. They work like friendly hunters for extremes: MIN() looks for the smallest item, and MAX() — the biggest one. You’re not limited to just numbers: you can hunt for the minimum and maximum strings or dates too.

-- General form for MIN and MAX functions
SELECT 
    MIN(column_name) AS min_value,
    MAX(column_name) AS max_value
FROM table_name;

Easy and simple! Now let’s check out how this magic works in different scenarios.

Syntax and Basic Examples

Numeric Data

Let’s say we have a table called employees with a column for employee salaries (salary).

We want to find out the minimum and maximum salaries.

Table employees

-- Example for numeric data
SELECT 
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees;

Result

Text Data

You can use MIN() and MAX() with text data too.

They compare values alphabetically (yep, even letters can be the "smallest" or "biggest").

Suppose we have a table students where the name column stores student names.

Table students

-- Example for text data
SELECT 
    MIN(name) AS first_name_in_order,
    MAX(name) AS last_name_in_order
FROM students;

Result

Dates and Time
Let’s find the earliest and latest date from the events table.

Table events

-- Example for working with dates
SELECT 
    MIN(event_date) AS earliest_date,
    MAX(event_date) AS latest_date
FROM events;

Result

Using MIN() and MAX() in Tables with NULL Values

If a column has NULL values, you can chill: SQL just ignores them when calculating.

This means MIN() and MAX() will only work with the "normal" values.

Table employees_with_null

-- Table with NULL
SELECT 
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees_with_null;

If there are NULL records in the salary column, they’re just ignored.

But if the WHOLE column is NULL, the result will be NULL.

Examples of Using MIN() and MAX() in Real Tasks

Finding the Oldest and Youngest Employee

Table employees

The employees table has a birth_date field that stores employee birth dates. Let’s find the oldest and youngest.

SELECT
    MIN(birth_date) AS oldest_employee,
    MAX(birth_date) AS youngest_employee
FROM employees;

Result:

Sales Analysis

The sales table has an amount field that stores sales amounts. Let’s find the smallest and largest sale.

SELECT
    MIN(amount) AS smallest_sale,
    MAX(amount) AS largest_sale
FROM sales;

Query result

Finding the First and Last Orders

If you have a table orders with a order_date field, you can easily find the first and last order dates.

Original table orders

SELECT
    MIN(order_date) AS first_order,
    MAX(order_date) AS last_order
FROM orders;

Query result:

Special Features of MIN() and MAX()

Comparing Text: when working with strings, MIN() and MAX() compare them alphabetically.

Working with NULL: NULL is ignored if there are other values in the column. But if all values are NULL, the result will be NULL.

Comparing Dates: SQL takes into account not just the year, but also the month, day, hours, and minutes.