MySql - Finding minimum and maximum values

Finding Minimum and Maximum Values (MIN, MAX) in MySQL

Introduction to MIN and MAX Functions in MySQL

In MySQL, the MIN and MAX functions are aggregate functions that help retrieve the minimum and maximum values from a set of records in a table. These functions are essential for data analysis, reporting, and decision-making processes where identifying extremes is necessary.

Understanding how to use MIN and MAX effectively can help you write powerful SQL queries to determine things like the highest salary, the earliest date, the cheapest product, or the latest transaction.

Syntax of MIN and MAX Functions

SELECT MIN(column_name) AS min_value
FROM table_name
WHERE condition;

SELECT MAX(column_name) AS max_value
FROM table_name
WHERE condition;

Here:

• column_name: The column from which to find the minimum or maximum value.
• table_name: The name of the table you are querying.
• condition: Optional. Filters the rows considered by the function.

Creating a Sample Dataset

For illustration, let’s create a products table.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2),
    stock_quantity INT,
    date_added DATE
);

Insert some sample data:

INSERT INTO products (product_id, product_name, category, price, stock_quantity, date_added) VALUES
(1, 'Laptop', 'Electronics', 800.00, 50, '2024-05-01'),
(2, 'Smartphone', 'Electronics', 500.00, 100, '2024-05-03'),
(3, 'Tablet', 'Electronics', 300.00, 80, '2024-05-05'),
(4, 'Headphones', 'Accessories', 80.00, 200, '2024-05-07'),
(5, 'Charger', 'Accessories', 25.00, 300, '2024-05-08'),
(6, 'Mouse', 'Accessories', 15.00, 150, '2024-05-10'),
(7, 'Monitor', 'Electronics', 250.00, 30, '2024-05-12');

Basic Usage of MIN and MAX

Finding the Cheapest Product

SELECT MIN(price) AS cheapest_price
FROM products;

Result

+----------------+
| cheapest_price |
+----------------+
| 15.00          |
+----------------+

Finding the Most Expensive Product

SELECT MAX(price) AS most_expensive_price
FROM products;

Result

+----------------------+
| most_expensive_price |
+----------------------+
| 800.00               |
+----------------------+

Finding Minimum and Maximum in Different Columns

Finding the Product with the Least Stock

SELECT MIN(stock_quantity) AS least_stock
FROM products;

Result

+-------------+
| least_stock |
+-------------+
| 30          |
+-------------+

Finding the Product with the Most Stock

SELECT MAX(stock_quantity) AS most_stock
FROM products;

Result

+------------+
| most_stock |
+------------+
| 300        |
+------------+

Using MIN and MAX with GROUP BY

When you want to find the minimum and maximum values for each group, combine MIN or MAX with GROUP BY.

Example: Minimum Price Per Category

SELECT category, MIN(price) AS min_price
FROM products
GROUP BY category;

Result

+-------------+-----------+
| category    | min_price |
+-------------+-----------+
| Accessories | 15.00     |
| Electronics | 250.00    |
+-------------+-----------+

Example: Maximum Stock Per Category

SELECT category, MAX(stock_quantity) AS max_stock
FROM products
GROUP BY category;

Result

+-------------+-----------+
| category    | max_stock |
+-------------+-----------+
| Accessories | 300       |
| Electronics | 100       |
+-------------+-----------+

MIN and MAX with Conditions

Example: Maximum Price of Electronics

SELECT MAX(price) AS max_electronics_price
FROM products
WHERE category = 'Electronics';

Result

+-----------------------+
| max_electronics_price |
+-----------------------+
| 800.00                |
+-----------------------+

Example: Minimum Price of Accessories

SELECT MIN(price) AS min_accessories_price
FROM products
WHERE category = 'Accessories';

Result

+-----------------------+
| min_accessories_price |
+-----------------------+
| 15.00                 |
+-----------------------+

Finding Corresponding Records for MIN and MAX Values

Sometimes, it is not enough to know just the minimum or maximum value — you may also want to know which record holds that value.

Example: Product with the Minimum Price

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

Result

+------------+-------------+-------------+-------+---------------+------------+
| product_id | product_name| category    | price | stock_quantity| date_added |
+------------+-------------+-------------+-------+---------------+------------+
| 6          | Mouse       | Accessories | 15.00 | 150           | 2024-05-10 |
+------------+-------------+-------------+-------+---------------+------------+

Example: Product with the Maximum Stock

SELECT *
FROM products
WHERE stock_quantity = (SELECT MAX(stock_quantity) FROM products);

MIN and MAX on Date Columns

The MIN and MAX functions are also useful for date fields, like finding the oldest or newest records.

Example: Earliest Product Added

SELECT MIN(date_added) AS earliest_date
FROM products;

Result

+---------------+
| earliest_date |
+---------------+
| 2024-05-01    |
+---------------+

Example: Latest Product Added

SELECT MAX(date_added) AS latest_date
FROM products;

Result

+-------------+
| latest_date |
+-------------+
| 2024-05-12  |
+-------------+

MIN and MAX with JOIN Operations

You can combine MIN and MAX with JOINs for advanced querying across multiple tables.

Assuming Another Table: suppliers

CREATE TABLE suppliers (
    supplier_id INT PRIMARY KEY,
    supplier_name VARCHAR(100)
);

INSERT INTO suppliers (supplier_id, supplier_name) VALUES
(1, 'Alpha Supplies'),
(2, 'Beta Electronics');

ALTER TABLE products ADD supplier_id INT;

UPDATE products
SET supplier_id = 1
WHERE category = 'Accessories';

UPDATE products
SET supplier_id = 2
WHERE category = 'Electronics';

Query to Get Maximum Price Per Supplier

SELECT s.supplier_name, MAX(p.price) AS max_price
FROM products p
JOIN suppliers s ON p.supplier_id = s.supplier_id
GROUP BY s.supplier_name;

Handling NULL Values with MIN and MAX

By default, MIN and MAX ignore NULL values.

Example with NULL Prices

INSERT INTO products (product_id, product_name, category, price, stock_quantity, date_added, supplier_id)
VALUES (8, 'USB Cable', 'Accessories', NULL, 100, '2024-05-15', 1);

SELECT MIN(price) AS min_price
FROM products;

The NULL price does not affect the result, as MIN only considers non-NULL values.

MIN and MAX with Window Functions (MySQL 8+)

Window functions provide row-wise MIN and MAX without collapsing the result into a single row.

Example: Maximum Price per Category Without GROUP BY

SELECT product_name, category, price,
MAX(price) OVER (PARTITION BY category) AS max_price_in_category
FROM products;

The MIN and MAX functions are fundamental tools in SQL and data analysis. They help pinpoint critical insights about the extremes in your dataset, whether those are values, dates, quantities, or other metrics. By mastering these functions, you can build powerful queries that reveal trends, opportunities, and anomalies in your data.

With proper use alongside GROUP BY, JOINs, window functions, and subqueries, MIN and MAX can greatly enhance the versatility and depth of your MySQL querying capabilities.