MySQL MAX(): Find the Highest Value in a Column

MySQL MAX() returns the highest non-NULL value produced by an expression. It can be used with numbers, dates, and text values.

SELECT MAX(mark) AS max_mark
FROM student;

For the sample rows below, the highest mark is 85.

MAX() Syntax Top ↑

SELECT MAX(expression)
FROM table_name;

MAX() ignores NULL values. If no non-NULL value is available, the aggregate result is NULL.

Basic MAX() Example Top ↑

The first examples use these rows from the student table:

SELECT MAX(mark) AS max_mark
FROM student;

The alias max_mark gives the aggregate result a useful column name.

Get the Complete Row with the Maximum Value Top ↑

This is a common requirement: find the highest mark and also return the student who has that mark.

Do not write:

-- Wrong or invalid for this purpose
SELECT id,
       name,
       class,
       MAX(mark) AS max_mark
FROM student;

With ONLY_FULL_GROUP_BY enabled, MySQL rejects this because the non-aggregate columns are not grouped. With permissive settings, those columns can come from an arbitrary row and are not guaranteed to belong to the maximum mark.

Use a subquery instead:

SELECT id,
       name,
       class,
       mark
FROM student
WHERE mark = (
    SELECT MAX(mark)
    FROM student
);

This returns every student whose mark equals the maximum value.

Return one row only Top ↑

If the requirement is specifically to return only one highest row, sort and limit the result:

SELECT id,
       name,
       class,
       mark
FROM student
ORDER BY mark DESC, id
LIMIT 1;

The secondary id sort provides deterministic tie-breaking. See top and second-highest value examples.

What Happens When Several Rows Share the Maximum? Top ↑

A subquery using WHERE mark = (SELECT MAX(mark) ...) returns all ties.

For example, if two students both have mark 96:

SELECT id,
       name,
       mark
FROM student
WHERE mark = (
    SELECT MAX(mark)
    FROM student
)
ORDER BY id;

Both rows are returned. This is usually preferable when "all students with the highest mark" is the real requirement.

Maximum Value in Each Group Top ↑

Use GROUP BY to calculate the maximum for each class:

SELECT class,
       MAX(mark) AS max_mark
FROM student
GROUP BY class
ORDER BY class;

This query returns the maximum mark for each group, but it still does not identify the full student row that produced each maximum.

Complete Row with the Maximum in Each Group Top ↑

To return the student rows that contain each class maximum, first calculate the maximum for each class and then join those results back to the source table:

SELECT s.id,
       s.name,
       s.class,
       s.mark
FROM student AS s
JOIN (
    SELECT class,
           MAX(mark) AS max_mark
    FROM student
    GROUP BY class
) AS m
  ON m.class = s.class
 AND m.max_mark = s.mark
ORDER BY s.class, s.id;

This correctly returns ties as separate rows when two students share the highest mark in the same class.

MySQL 8.0 window-function alternative Top ↑

With MySQL 8.0+, RANK() provides another clear solution:

WITH ranked AS (
    SELECT id,
           name,
           class,
           mark,
           RANK() OVER (
               PARTITION BY class
               ORDER BY mark DESC
           ) AS rnk
    FROM student
)
SELECT id,
       name,
       class,
       mark
FROM ranked
WHERE rnk = 1
ORDER BY class, id;

RANK() keeps tied highest rows. Use ROW_NUMBER() instead only when the requirement is exactly one row per group and you have defined a deliberate tie-breaker.

MAX() with WHERE Top ↑

A WHERE clause filters the rows before MAX() is calculated.

SELECT MAX(mark) AS max_mark
FROM student
WHERE class = 'Four';

For the sample table, the maximum mark in class Four is 75.

If you select class as well, group by it:

SELECT class,
       MAX(mark) AS max_mark
FROM student
WHERE class = 'Four'
GROUP BY class;

MAX() with JOIN Top ↑

When data comes from related tables, aggregate only the columns that belong to the grouping logic. Do not select an unrelated product column beside MAX(qty) unless the query also identifies which row produced that maximum.

For example, find the maximum quantity recorded for each store:

SELECT a.store,
       MAX(a.qty) AS max_qty
FROM sales AS a
GROUP BY a.store
ORDER BY a.store;

If you then need the matching product row and its price, calculate the maximum by store first and join back to sales, followed by the product table:

SELECT s.store,
       s.qty AS max_qty,
       p.product,
       p.price,
       p.price * s.qty AS total_price
FROM sales AS s
JOIN (
    SELECT store,
           MAX(qty) AS max_qty
    FROM sales
    GROUP BY store
) AS m
  ON m.store = s.store
 AND m.max_qty = s.qty
LEFT JOIN products AS p
  ON p.p_id = s.p_id
ORDER BY s.store, s.p_id;

This avoids the old pattern of grouping only by store while selecting a product that is not functionally tied to the aggregate result.

MAX() as a Window Function Top ↑

GROUP BY collapses multiple source rows into one result row per group. A window-function form of MAX() keeps each detail row while adding the group maximum beside it:

SELECT id,
       name,
       class,
       mark,
       MAX(mark) OVER (
           PARTITION BY class
       ) AS class_max
FROM student
ORDER BY class, id;

This is useful for reports that need both the original row and the class maximum. See the existing OVER() and PARTITION BY tutorial.

MAX() on Text Values Top ↑

MAX() can also operate on character columns. MySQL compares text according to the column's collation.

SELECT MAX(name) AS max_name
FROM student;

This returns the greatest text value according to the active collation. It is not a numeric comparison and should not be described simply as "the highest alphabet."

Numbers Stored in VARCHAR Top ↑

If numbers are stored as text, MAX() compares the text representation rather than the numeric value.

For these VARCHAR values:

'1', '2', '3', '4', '5', '6', '12', '13'

a text comparison can produce 6 as the maximum because the values are compared lexically.

Convert the values to a numeric type before taking the maximum:

SELECT MAX(
           CONVERT(t1, UNSIGNED)
       ) AS numeric_max
FROM max_value;

The result is 13.

Sample table:

CREATE TABLE max_value (
    t1 VARCHAR(2) NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

INSERT INTO max_value
    (t1)
VALUES
    ('1'),
    ('2'),
    ('3'),
    ('4'),
    ('5'),
    ('6'),
    ('12'),
    ('13');

See MySQL CONVERT() for more conversion examples.

MAX() on DATE and DATETIME Top ↑

On a DATE or DATETIME column, MAX() returns the latest non-NULL date/time value.

SELECT MAX(exam_dt) AS latest_exam
FROM student_mark;

Latest exam date for each month Top ↑

When the table can contain more than one year, grouping only by month number mixes January from different years. Group by both year and month:

SELECT YEAR(exam_dt) AS exam_year,
       MONTH(exam_dt) AS exam_month,
       MAX(exam_dt) AS latest_exam
FROM student_mark
GROUP BY YEAR(exam_dt),
         MONTH(exam_dt)
ORDER BY exam_year, exam_month;

Download the existing student_mark sample SQL data.

NULL Values and MAX() Top ↑

MAX() ignores NULL values. If every selected value is NULL, or no rows satisfy the filter, the result is NULL.

SELECT MAX(mark) AS max_mark
FROM student
WHERE id > 1000;

If the application needs a fallback value, use COALESCE() only when that fallback has a correct business meaning:

SELECT COALESCE(
           MAX(mark),
           0
       ) AS max_mark
FROM student
WHERE id > 1000;

Store Maximum Rows in Another Table Top ↑

You can create a new table containing the row or rows with the maximum mark:

CREATE TABLE student_max AS
SELECT id,
       name,
       class,
       mark
FROM student
WHERE mark = (
    SELECT MAX(mark)
    FROM student
);

If you need a new table with the source table's structure first, use:

CREATE TABLE student_max
LIKE student;

INSERT INTO student_max
    (id, name, class, mark)
SELECT id,
       name,
       class,
       mark
FROM student
WHERE mark = (
    SELECT MAX(mark)
    FROM student
);

If the destination table already exists, use an INSERT ... SELECT approach.

Using MAX() in a PHP PDO Script Top ↑

After connecting to MySQL with PDO, a static grouped MAX query can be executed with query():

<?php
$sql="SELECT class,
             MAX(mark) AS max_mark
      FROM student
      GROUP BY class
      ORDER BY class";

$stmt=$dbo->query($sql);

foreach($stmt as $row){
    echo '<p>'
        .htmlspecialchars(
            $row['class'],
            ENT_QUOTES,
            'Windows-1252'
        )
        .' : '
        .htmlspecialchars(
            (string)$row['max_mark'],
            ENT_QUOTES,
            'Windows-1252'
        )
        .'</p>';
}

This query contains no external values, so query() is appropriate. When the class or another condition comes from user input, use a prepared statement and bind the value.

Performance Notes Top ↑

• A simple MAX(indexed_column) query can often benefit from an appropriate index, depending on the filter, table structure, and optimizer plan.
• If the query filters first, indexes that support the WHERE condition can matter as much as the MAX expression.
• Grouping by functions such as YEAR(date_col) and MONTH(date_col) can require more work than grouping by a stored/indexed reporting key on very large tables.
• Converting a VARCHAR column to a number during every query adds work. Use a numeric datatype when the data is fundamentally numeric.
• Use EXPLAIN and real query measurements for performance decisions rather than assuming every MAX query requires a special optimization.

Common MAX() Mistakes Top ↑

Selecting unrelated columns beside MAX() Top ↑

SELECT name, MAX(mark) FROM student does not reliably return the name belonging to the highest mark. Use a subquery, JOIN, or window function.

Forgetting about ties Top ↑

A maximum value can belong to more than one row. Decide whether the requirement is to return every tied row or exactly one row with an explicit tie-breaker.

Grouping only by month number across several years Top ↑

GROUP BY MONTH(exam_dt) combines the same month from different years. Include the year when the data spans multiple years.

Using MAX() on numeric values stored as text Top ↑

VARCHAR values are compared as text. Store numeric data in a numeric datatype whenever practical.

Assuming CREATE TABLE ... AS SELECT copies the complete schema Top ↑

It does not preserve all indexes, constraints, AUTO_INCREMENT properties, triggers, and other source-table attributes.

Using MAX(id) to find the row your application just inserted Top ↑

Do not use MAX(id) as an insert-ID mechanism. Another connection may insert a newer row. Use LAST_INSERT_ID() or the database API's insert-ID feature.

Related Tutorials and Downloads Top ↑

Download the full Plus2net student table SQL dump

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: