MIN and MAX functions in MySQL

Introduction

MIN and MAX help find the smallest or largest value in a list of data. They make it easy to spot extremes.

Finding the lowest price of products in a store.

Getting the highest score in a game leaderboard.

Checking the earliest or latest date in a list of events.

Finding the minimum or maximum temperature recorded in a month.

Syntax

MySQL

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

Use MIN to get the smallest value in a column.

Use MAX to get the largest value in a column.

Examples

This finds the lowest price in the products table.

MySQL

SELECT MIN(price) FROM products;

This finds the highest score in the game_scores table.

MySQL

SELECT MAX(score) FROM game_scores;

This finds the earliest birthdate in the users table.

MySQL

SELECT MIN(birthdate) FROM users;

This finds the highest salary in the employees table.

MySQL

SELECT MAX(salary) FROM employees;

Sample Program

This example creates a sales table, adds some sales amounts, then finds the smallest and largest sale.

MySQL

CREATE TABLE sales (
  id INT,
  amount DECIMAL(10,2)
);

INSERT INTO sales VALUES (1, 100.50), (2, 250.00), (3, 75.25), (4, 300.00);

SELECT MIN(amount) AS LowestSale, MAX(amount) AS HighestSale FROM sales;

Important Notes

MIN and MAX ignore NULL values automatically.

You can use these functions with numbers, dates, and even text (alphabetically).

Summary

MIN finds the smallest value in a column.

MAX finds the largest value in a column.

They help quickly find extremes in your data.

Practice

1. What does the MIN function do in MySQL?

easy

A. Finds the smallest value in a column

B. Finds the largest value in a column

C. Counts the number of rows

D. Calculates the average value

Solution

1. Step 1: Understand the purpose of MIN function

   The MIN function is designed to find the smallest value in a specified column of a table.

2. Step 2: Compare with other aggregate functions

   Unlike MAX which finds the largest value, MIN specifically returns the minimum value.

3. Final Answer:

   Finds the smallest value in a column -> Option A

4. Quick Check:

   MIN = smallest value [OK]

Hint: MIN always returns the smallest value in a column [OK]

Common Mistakes:

• Confusing MIN with MAX
• Thinking MIN counts rows
• Assuming MIN calculates average

2. Which of the following is the correct syntax to find the maximum value in the price column of a table named products?

easy

A. SELECT MAX(price) IN products;

B. SELECT MAX price FROM products;

C. SELECT MAX(price) FROM products WHERE;

D. SELECT MAX(price) FROM products;

Solution

1. Step 1: Recall correct SQL aggregate function syntax

   The correct syntax for using MAX is: SELECT MAX(column_name) FROM table_name;

2. Step 2: Check each option for syntax errors

   SELECT MAX(price) FROM products; uses correct parentheses and FROM clause. Options A, B, and D have syntax errors or incomplete clauses.

3. Final Answer:

   SELECT MAX(price) FROM products; -> Option D

4. Quick Check:

   MAX(price) syntax = SELECT MAX(price) FROM table [OK]

Hint: Use parentheses around column name with MAX [OK]

Common Mistakes:

• Omitting parentheses in MAX function
• Using IN instead of FROM
• Leaving incomplete WHERE clause

3. Given the table employees with a column salary containing values (3000, 4500, 2500, 5000), what will this query return?
SELECT MIN(salary), MAX(salary) FROM employees;

medium

A. (4500, 3000)

B. (2500, 5000)

C. (5000, 2500)

D. (3000, 4500)

Solution

1. Step 1: Identify the smallest and largest salary values

   The salaries are 3000, 4500, 2500, and 5000. The smallest is 2500 and the largest is 5000.

2. Step 2: Understand the query output format

   The query returns a single row with two columns: MIN(salary) and MAX(salary), so the output is (2500, 5000).

3. Final Answer:

   (2500, 5000) -> Option B

4. Quick Check:

   MIN=2500, MAX=5000 [OK]

Hint: MIN returns smallest, MAX returns largest value [OK]

Common Mistakes:

• Mixing up MIN and MAX values
• Assuming output is multiple rows
• Confusing order of returned values

4. What is wrong with this query to find the minimum age from the users table?
SELECT MIN age FROM users;

medium

A. Missing parentheses around the column name

B. Incorrect table name

C. MIN cannot be used with numeric columns

D. Missing WHERE clause

Solution

1. Step 1: Check the syntax of the MIN function usage

   The MIN function requires parentheses around the column name, like MIN(age).

2. Step 2: Identify the error in the query

   The query is missing parentheses, so it will cause a syntax error.

3. Final Answer:

   Missing parentheses around the column name -> Option A

4. Quick Check:

   MIN requires parentheses [OK]

Hint: Always use parentheses with MIN and MAX [OK]

Common Mistakes:

• Omitting parentheses in function calls
• Assuming WHERE clause is mandatory
• Thinking MIN can't be used on numbers

5. You have a table orders with columns order_id, customer_id, and amount. How would you write a query to find the highest order amount for each customer?

hard

A. SELECT customer_id, amount FROM orders WHERE amount = MAX(amount);

B. SELECT MAX(amount) FROM orders GROUP BY customer_id;

C. SELECT customer_id, MAX(amount) FROM orders GROUP BY customer_id;

D. SELECT customer_id, MAX(amount) FROM orders;

Solution

1. Step 1: Understand the requirement to group by customer

   We want the highest order amount per customer, so we must group results by customer_id.

2. Step 2: Use MAX with GROUP BY

   Using MAX(amount) with GROUP BY customer_id returns the maximum amount for each customer.

3. Step 3: Check each option

   SELECT customer_id, MAX(amount) FROM orders GROUP BY customer_id; correctly groups by customer_id and selects MAX(amount). SELECT MAX(amount) FROM orders GROUP BY customer_id; misses customer_id in SELECT. SELECT customer_id, amount FROM orders WHERE amount = MAX(amount); uses invalid WHERE with aggregate. SELECT customer_id, MAX(amount) FROM orders; lacks GROUP BY.

4. Final Answer:

   SELECT customer_id, MAX(amount) FROM orders GROUP BY customer_id; -> Option C

5. Quick Check:

   MAX with GROUP BY returns max per group [OK]

Hint: Use GROUP BY with MAX to get max per group [OK]

Common Mistakes:

• Not using GROUP BY when aggregating per group
• Using WHERE with aggregate functions incorrectly
• Selecting columns not in GROUP BY without aggregation