Home / SQL / MIN and MAX

MIN and MAX

Overview

The MIN() and MAX() functions are aggregate functions that return the minimum and maximum values from a set of values, respectively. These functions are useful for finding the smallest or largest value in a column.

MIN() and MAX() can be used with numeric, date, and text data types, making them versatile for various queries.

MIN Function

The MIN() function returns the smallest value in a column.

MIN Syntax

SELECT MIN(column_name)
FROM table_name
WHERE condition;

MAX Function

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

MAX Syntax

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Examples with Numeric Values

Example 1: Find Minimum Price

Lowest product price

SELECT MIN(price) AS lowest_price
FROM Products;

Example 2: Find Maximum Price

Highest product price

SELECT MAX(price) AS highest_price
FROM Products;

Example 3: Find Min and Max Together

Price range

SELECT 
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    MAX(price) - MIN(price) AS price_range
FROM Products;

Examples with Date Values

Example 1: Earliest Date

Oldest order

SELECT MIN(order_date) AS earliest_order
FROM Orders;

Example 2: Latest Date

Most recent order

SELECT MAX(order_date) AS latest_order
FROM Orders;

Examples with Text Values

MIN() and MAX() can also work with text, returning the first and last values alphabetically:

Text comparison

-- First alphabetically
SELECT MIN(product_name) AS first_product
FROM Products;

-- Last alphabetically
SELECT MAX(product_name) AS last_product
FROM Products;

MIN and MAX with WHERE

You can combine MIN() and MAX() with WHERE to filter before finding min/max:

Filtered min/max

-- Lowest price in Electronics category
SELECT MIN(price) AS min_electronics_price
FROM Products
WHERE category = 'Electronics';

-- Highest price above $100
SELECT MAX(price) AS max_price_above_100
FROM Products
WHERE price > 100;

MIN and MAX with GROUP BY

Use MIN() and MAX() with GROUP BY to find min/max per group:

Min/Max per category

SELECT 
    category,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM Products
GROUP BY category;

Example Result:

Sample Result

┌──────────────┬───────────┬───────────┐
│ category     │ min_price │ max_price │
├──────────────┼───────────┼───────────┤
│ Electronics  │ 50        │ 999       │
│ Clothing     │ 20        │ 200       │
│ Books        │ 10        │ 50        │
└──────────────┴───────────┴───────────┘

Finding Full Records with MIN/MAX

To get the full record with the min or max value, use a subquery:

Full record with minimum price

SELECT *
FROM Products
WHERE price = (SELECT MIN(price) FROM Products);

Alternative: Using ORDER BY and LIMIT

Using ORDER BY and LIMIT

-- Cheapest product
SELECT *
FROM Products
ORDER BY price ASC
LIMIT 1;

-- Most expensive product
SELECT *
FROM Products
ORDER BY price DESC
LIMIT 1;

MIN and MAX with NULL Values

MIN() and MAX() ignore NULL values:

NULL handling

-- NULL values are ignored
SELECT MIN(price), MAX(price)
FROM Products;
-- Only non-NULL prices are considered

Common Use Cases

Finding the cheapest or most expensive product
Determining the oldest or newest order date
Calculating price ranges for reporting
Finding earliest or latest transactions
Analyzing data ranges in datasets

Best Practices

Use aliases: Give meaningful names to MIN/MAX results
Combine with other aggregates: Use with AVG, COUNT for comprehensive analysis
Consider indexing: Index columns used in MIN/MAX for better performance
Use WHERE first: Filter before aggregating for better performance
Handle NULLs: Be aware that NULLs are excluded from calculations

Performance Considerations

Indexes help: Indexes on columns used in MIN/MAX improve performance
Filter first: Use WHERE to reduce rows before aggregation
Avoid in WHERE: Don't use MIN/MAX in WHERE clause (use subqueries or HAVING instead)

Next Steps

Learn about counting records with COUNT, or explore calculating sums and averages with SUM and AVG.

← Delete Count →