SQL MAX Function – Syntax and Examples

SQL MAX Function

The SQL MAX function is used to find the maximum value in a column. It works with numeric, date, and text data types, returning the largest value in the specified column.

The MAX function is frequently used with the GROUP BY clause to get maximum values for each group in the dataset.

In this tutorial, we will go through SQL MAX Function, its syntax, and how to use this function in SQL statements, with the help of well detailed examples.

---

Syntax of SQL MAX Function

The basic syntax of the SQL MAX function is as follows:

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

Each part of this syntax has a specific purpose:

• SELECT: Specifies the column to retrieve the maximum value from.
• MAX(column_name): The MAX function, which returns the largest value in the specified column.
• AS max_value: An alias for the maximum value column in the results.
• FROM: Specifies the table from which to retrieve data.
• WHERE (optional): Filters the data based on specified conditions before applying the MAX function.

---

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the MAX function in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality along with a grades table that records students’ scores.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Create a grades table:

CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

5. Insert sample data into the students and grades tables:

INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

INSERT INTO grades (student_id, subject, score)
VALUES
(1, 'Math', 85),
(2, 'Math', 78),
(3, 'Science', 90),
(4, 'Math', 88),
(5, 'Science', 70);

---

Examples: Using MAX Function in Queries

Now, let’s explore different scenarios of using the MAX function with these tables.

---

1. Finding the Maximum Score Across All Subjects

To find the highest score in the grades table:

SELECT MAX(score) AS max_score
FROM grades;

This query returns the highest score across all subjects in the grades table, displaying the maximum score.

---

2. Finding the Maximum Age in the Students Table

To find the oldest student in the students table:

SELECT MAX(age) AS max_age
FROM students;

This query returns the largest age value in the students table, displaying the age of the oldest student.

---

3. Using MAX with a WHERE Clause

To find the maximum score for the subject “Math” only:

SELECT MAX(score) AS max_math_score
FROM grades
WHERE subject = 'Math';

This query finds the maximum score for “Math” in the grades table, filtering scores only for that subject.

Reference: SQL WHERE Clause

---

4. Using MAX with GROUP BY to Find Maximum Scores per Subject

To find the maximum score for each subject:

SELECT subject, MAX(score) AS max_score
FROM grades
GROUP BY subject;

This query returns the maximum score for each subject in the grades table, displaying the highest score per subject.

Reference: SQL GROUP BY

---

FAQs for SQL MAX

1. What does the SQL MAX function do?

The MAX function finds the maximum value in a specified column, returning the largest value from a dataset.

2. Can I use MAX with non-numeric data?

Yes, MAX can be used with text and date data. For text, it returns the lexicographically largest value, and for dates, it returns the latest date.

3. How do I find the maximum value for each group?

Use the GROUP BY clause with MAX to find the maximum value for each group in the dataset.

4. Can MAX be used with WHERE conditions?

Yes, you can filter results before applying MAX by using a WHERE clause to specify conditions.

5. Does MAX consider NULL values?

No, MAX ignores NULL values in the column and only considers non-NULL values when calculating the maximum.