MySQL MIN and MAX

Key MIN/MAX Concepts

SELECT MIN(salary)
FROM employees;

SELECT MAX(salary)
FROM employees;

SELECT MIN(order_date),
       MAX(order_date)
FROM orders;

SELECT MIN(name),
       MAX(name)
FROM customers;

🔹 Using MIN Function

The MIN() function scans through all values in a column and returns the smallest one. It works with numbers, dates, and text. NULL values are ignored in the calculation, making it safe to use on columns with missing data.

-- Find lowest product price
SELECT MIN(price) AS lowest_price
FROM products;

-- Find earliest order date
SELECT MIN(order_date) AS first_order
FROM orders;

-- Find alphabetically first customer name
SELECT MIN(customer_name) AS first_name
FROM customers;

🔹 Using MAX Function

The MAX() function finds the largest value in a column. Like MIN(), it works with numeric, date, and text data types. For text, it returns the value that comes last alphabetically. NULL values are automatically excluded from the result.

-- Find highest salary
SELECT MAX(salary) AS highest_salary
FROM employees;

-- Find most recent order
SELECT MAX(order_date) AS latest_order
FROM orders;

-- Find product with highest stock
SELECT MAX(stock_quantity) AS max_stock
FROM inventory;

🔹 MIN and MAX Together

You can use both functions in the same query to get a range of values. This is useful for understanding the spread of your data, such as price ranges, date ranges, or score ranges in a single query result.

-- Get price range
SELECT 
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    MAX(price) - MIN(price) AS price_range
FROM products;

-- Get age range of employees
SELECT 
    MIN(age) AS youngest,
    MAX(age) AS oldest
FROM employees;

🔹 MIN/MAX with GROUP BY

Combine MIN() and MAX() with GROUP BY to find extremes within different categories. This lets you compare ranges across departments, product categories, regions, or any other grouping, providing insights into variations between groups.

-- Find salary range by department
SELECT 
    department,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department;

-- Find price range by category
SELECT 
    category,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive
FROM products
GROUP BY category;

🔹 MIN/MAX with WHERE Clause

Filter your data before finding MIN or MAX values using the WHERE clause. This helps you find extremes within specific subsets of data, such as the highest price in a particular category or the earliest order from a specific customer.

-- Find highest price in Electronics category
SELECT MAX(price) AS max_electronics_price
FROM products
WHERE category = 'Electronics';

-- Find earliest order in 2024
SELECT MIN(order_date) AS first_2024_order
FROM orders
WHERE YEAR(order_date) = 2024;

-- Find lowest salary in Sales department
SELECT MIN(salary) AS min_sales_salary
FROM employees
WHERE department = 'Sales';

🔹 Practical Examples

Real-world applications of MIN and MAX functions in business scenarios and data analysis:

-- Find product with lowest stock (needs reorder)
SELECT product_name, MIN(stock_quantity) AS lowest_stock
FROM inventory
GROUP BY product_name
ORDER BY lowest_stock ASC
LIMIT 5;

-- Get temperature range for each city
SELECT 
    city,
    MIN(temperature) AS coldest,
    MAX(temperature) AS hottest
FROM weather_data
GROUP BY city;

-- Find newest and oldest employees
SELECT 
    MIN(hire_date) AS first_hired,
    MAX(hire_date) AS last_hired
FROM employees;