Skip to main content

MIN(), MAX()

Learning Focus

Use this lesson to understand MIN(), MAX() with practical syntax and examples.

Concept Overview​

What Are MIN() and MAX()?​

  • MIN() and MAX() are aggregate functions in MySQL that return the smallest and largest value in a given column, respectively.
  • They are often used in SELECT queries to derive summary insights from data.

Why Are They Important?​

  • These functions help quickly answer questions like:
    • "What is the lowest price of a product?"
    • "Which employee has the highest salary?"
  • They're critical in data analysis, reporting, and decision-making processes.

Where They Fit In​

  • These functions are part of aggregate operations, alongside others like SUM(), COUNT(), AVG().
  • Commonly used with:
    • GROUP BY (e.g., finding max per category)
    • HAVING (filtering aggregated results)
    • Subqueries (e.g., finding rows with the max value)

Basic Syntax & Rules​

Syntax​

SELECT MIN(column_name) FROM table_name;
SELECT MAX(column_name) FROM table_name;

Key Parameters​

  • column_name: The column you want to analyze.
  • table_name: The table containing your data.

Notes & Limitations​

  • NULL values are ignored by both functions.
  • They only return one value unless combined with GROUP BY.
  • Cannot apply to text for logical ordering (e.g., "most valuable string") unless used alphabetically.

Step-by-Step Examples​

Sample Database/Table Data Used in the Examples​

Use this sample data first so Example 1 until the end runs with consistent results.

-- Products (used in Example 1 and Retail Analysis)
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
);

INSERT INTO products (id, name, price) VALUES
(1, 'Mouse', 9.99),
(2, 'Keyboard', 24.99),
(3, 'Monitor', 199.99),
(4, 'Headset', 49.50);

-- Employees (used in Example 2, 3, 4 and Employee Management)
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'Engineering', 100000.00),
(2, 'Bob', 'Engineering', 120000.00),
(3, 'Charlie', 'Sales', 85000.00),
(4, 'Diana', 'HR', 65000.00),
(5, 'Eve', 'Sales', 79000.00);

-- Customers (used in Customer Engagement use case)
CREATE TABLE customers (
    id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    registration_date DATE
);

INSERT INTO customers (id, customer_name, registration_date) VALUES
(1, 'Raka', '2023-03-15'),
(2, 'Maya', '2022-11-02'),
(3, 'Irfan', '2024-01-20');

-- Branches (used in Performance Benchmarks use case)
CREATE TABLE branches (
    branch_id INT,
    month_label VARCHAR(7),
    sales DECIMAL(12, 2)
);

INSERT INTO branches (branch_id, month_label, sales) VALUES
(1, '2025-01', 15000.00),
(1, '2025-02', 17250.00),
(2, '2025-01', 22000.00),
(2, '2025-02', 19800.00);

-- Inventory (used in Inventory Control use case)
CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    quantity INT
);

INSERT INTO inventory (product_id, product_name, quantity) VALUES
(1, 'Mouse', 150),
(2, 'Keyboard', 80),
(3, 'Monitor', 20),
(4, 'Headset', 55);

Example 1: Simple Use​

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

Sample Output:

+-------------+
| LowestPrice |
+-------------+
|       9.99  |
+-------------+

Example 2: Using MAX()​

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

Output:

+---------------+
| HighestSalary |
+---------------+
|       120000  |
+---------------+

Example 3: Grouped Data​

-- Find the highest salary per department
SELECT department, MAX(salary) AS MaxSalary
FROM employees
GROUP BY department;

Output:

+-------------+-----------+
| department  | MaxSalary |
+-------------+-----------+
| Sales       | 85000     |
| Engineering | 120000    |
| HR          | 65000     |
+-------------+-----------+

Example 4: Using Subquery​

-- Find employee(s) with the highest salary
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Practical Use Cases​

1. Retail Analysis​

  • Identify cheapest or most expensive product:
SELECT name FROM products WHERE price = (SELECT MAX(price) FROM products);

2. Employee Management​

  • Highlight top performer salary-wise:
SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

3. Customer Engagement​

  • Find the earliest registration date:
SELECT MIN(registration_date) FROM customers;

4. Performance Benchmarks​

  • Track best-performing branch:
SELECT branch_id, MAX(sales) FROM branches GROUP BY branch_id;

5. Inventory Control​

  • Identify lowest stock item:
SELECT product_name FROM inventory WHERE quantity = (SELECT MIN(quantity) FROM inventory);

Common Mistakes & Troubleshooting​

Mistake 1: Using MIN()/MAX() with WHERE incorrectly​

[X]

SELECT MIN(price) FROM products WHERE MIN(price) > 100;

[OK]

SELECT MIN(price) FROM products WHERE price > 100;

Mistake 2: Forgetting GROUP BY​

[X]

SELECT department, MAX(salary) FROM employees;

[OK]

SELECT department, MAX(salary) FROM employees GROUP BY department;

Mistake 3: Not handling NULLs​

Tip: Be aware NULL values are ignored. If you need to include them, use COALESCE().

Mistake 4: Misunderstanding return values​

  • These functions do not return multiple rows unless used with GROUP BY or in a subquery.

Best Practices​

Performance Tips​

  • Index the column used with MIN() or MAX() for faster queries.
  • Avoid using these in correlated subqueries if the dataset is large.

When to Use​

  • Summarizing datasets
  • Highlighting extremes (top performer, least stock)

When NOT to Use​

  • When you need multiple top/bottom values (use ORDER BY ... LIMIT instead)
  • When working with complex conditional logic-use CASE or JOINs

Hands-On Practice​

Sample Table Setup​

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees VALUES
(1, 'Alice', 'Engineering', 100000),
(2, 'Bob', 'Engineering', 120000),
(3, 'Charlie', 'Sales', 85000),
(4, 'Diana', 'HR', 65000),
(5, 'Eve', 'Sales', 79000);

Exercise 1: Simple MIN()​

Question: What is the lowest salary?

SELECT MIN(salary) FROM employees;

Answer: 65000

Exercise 2: MAX with GROUP BY​

Question: What's the highest salary per department?

SELECT department, MAX(salary) FROM employees GROUP BY department;

Exercise 3: Who earns the highest salary?​

SELECT name FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Connection to Other Concepts​

  • Prerequisite Concepts:
    • SELECT statements
    • Filtering with WHERE
    • GROUP BY and basic aggregation
  • Closely Related Topics:
    • COUNT(), SUM(), AVG() (other aggregate functions)
    • Subqueries
    • Indexing for performance
  • Next to Learn:
    • HAVING clause for filtering groups
    • Window functions (ROW_NUMBER(), RANK()) for top-N analysis

Visual Learning Diagram (Mermaid)​

graph TD
  A[Basic SELECT Queries] --> B[Filtering with WHERE]
  B --> C[GROUP BY]
  C --> D[Aggregate Functions]
  D --> E[MIN and MAX]
  D --> F[SUM()]
  D --> G[COUNT()]
  D --> H[AVG()]
  E --> I[Subqueries]
  E --> J[Real-world Queries]
  E --> K[Performance Optimization]
  K --> L[Indexes]
  E --> M[Window Functions]
  M --> N((Advanced Aggregations))
  N --> O[Learning Path Forward]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I,J,K,L,M,N,O allNodes
  class E stroke-width:4px

Common Pitfalls​

PitfallConsequencePrevention
Executing queries without validating sample rowsLogic errors reach production data or reportsStart with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behaviorAggregations and filters return misleading resultsTest with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schemaQueries fail due to missing columns/indexesVerify structure with DESCRIBE table_name; and adapt query design

Quick Reference​

SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM products;
SELECT department, MAX(salary) FROM employees GROUP BY department;

What's Next​