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);