• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL MIN and MAX

← Previous Next →

In MySQL, MIN and MAX are aggregate functions used to find the smallest and largest values in a column, respectively. These functions are particularly useful when you want to identify extremes in data, such as the highest score, the lowest salary, or the earliest and latest dates.

Unlike simple SELECT queries, MIN and MAX perform calculations on a group of values and return a single result. They are commonly used in reporting, data analysis, and decision-making.

Basic Syntax

The basic syntax for MIN and MAX is:

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

  • column_name specifies the column to evaluate.

  • table_name is the table containing the column.

Example:

SELECT MIN(age) FROM students;
SELECT MAX(age) FROM students;

  • Returns the youngest and oldest student age from the students table.

Using MIN and MAX with WHERE

You can filter data before calculating MIN or MAX using the WHERE clause:

SELECT MIN(age) FROM students
WHERE city = 'Mumbai';

SELECT MAX(age) FROM students
WHERE city = 'Delhi';

  • Finds the youngest student in Mumbai and the oldest student in Delhi.

  • WHERE allows you to focus on specific subsets of data.

Using MIN and MAX with GROUP BY

MIN and MAX can be used with GROUP BY to find extremes for each category:

SELECT city, MIN(age) AS youngest, MAX(age) AS oldest
FROM students
GROUP BY city;

  • Returns the youngest and oldest student in each city.

  • AS is used to assign aliases for readability.

Example result:

city youngest oldest
Mumbai 19 22
Delhi 20 25
Pune 18 21

  • This is very helpful for category-based reporting.

MIN and MAX on Dates

MIN and MAX can also be applied to date or datetime columns:

SELECT MIN(join_date) AS earliest_join, MAX(join_date) AS latest_join
FROM students;

  • Finds the earliest and latest joining date.

  • Useful in attendance tracking, employee records, and timelines.

MIN and MAX on Expressions

You can also use MIN and MAX on expressions, not just columns:

SELECT MIN(age + 1) AS min_next_year,
       MAX(age + 1) AS max_next_year
FROM students;

  • Calculates the minimum and maximum age after one year.

  • Useful for dynamic calculations within aggregate functions.

MIN and MAX with Multiple Columns

MySQL evaluates one column at a time, but you can combine results using subqueries or multiple SELECT statements:

SELECT name, age
FROM students
WHERE age = (SELECT MIN(age) FROM students);

SELECT name, age
FROM students
WHERE age = (SELECT MAX(age) FROM students);

  • Retrieves the full row of the youngest and oldest student.

  • Subqueries allow you to return additional details beyond just the extreme value.

Important Points

  1. NULL values are ignored by MIN and MAX.

    SELECT MIN(age) FROM students;  -- NULL ages are not considered

  2. MIN and MAX can be used with numeric, string, or date types.

  3. For strings, MIN returns the alphabetically first value, and MAX returns the alphabetically last value.

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

  4. Combine MIN/MAX with GROUP BY for category-specific analysis.

  5. Use aliases for better readability in query results.

Real-Life Examples

Suppose the students table contains:

id name age city join_date
1 Aditi 19 Mumbai 2023-01-10
2 Meera 20 Pune 2023-03-15
3 Pooja 18 Pune 2022-12-05
4 Riya 21 Delhi 2023-02-20
5 Ananya 22 Mumbai 2023-01-25

Example 1 – Youngest and oldest student overall:

SELECT MIN(age) AS youngest, MAX(age) AS oldest
FROM students;

  • Result: youngest = 18, oldest = 22

Example 2 – Youngest and oldest student by city:

SELECT city, MIN(age) AS youngest, MAX(age) AS oldest
FROM students
GROUP BY city;

  • Result:

    • Mumbai → 19, 22

    • Pune → 18, 20

    • Delhi → 21, 21

Example 3 – Earliest and latest joining date:

SELECT MIN(join_date) AS earliest, MAX(join_date) AS latest
FROM students;

  • Result: earliest = 2022-12-05, latest = 2023-03-15

Example 4 – Full row of youngest student:

SELECT name, age, city
FROM students
WHERE age = (SELECT MIN(age) FROM students);

  • Returns Pooja, 18, Pune

Example 5 – Alphabetically first and last name:

SELECT MIN(name) AS first_name, MAX(name) AS last_name
FROM students;

  • Result: first_name = Aditi, last_name = Riya

Summary of the Tutorial

  • MIN returns the smallest value; MAX returns the largest value in a column.

  • Can be used on numeric, string, or date columns.

  • NULL values are ignored in calculations.

  • Combine MIN and MAX with WHERE, GROUP BY, or subqueries for detailed analysis.

  • Useful in real-life scenarios like finding highest scores, lowest salaries, earliest or latest dates, and category-based extremes.

  • Using aliases improves readability, especially in reports.

Mastering MIN and MAX is essential for data analysis and reporting, as it allows you to quickly identify the boundaries in your datasets and make informed decisions.

Practice Questions

  1. Find the youngest student in the students table.

  2. Find the oldest student in the students table.

  3. Find the minimum and maximum age of students in Mumbai.

  4. Find the minimum and maximum joining dates of all students.

  5. Retrieve the full row of the student with the minimum age.

  6. Retrieve the full row of the student with the maximum age.

  7. Find the youngest and oldest student in each city using GROUP BY.

  8. Find the alphabetically first and last student names.

  9. Find the minimum and maximum age after adding 1 year to each student’s age.

  10. Retrieve the oldest student who joined after 2023-01-01.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top