MAX()

MAX() Function in MySQL

Introduction
Identifying highest values across datasets is crucial for ranking and analysis.
MAX() function returns largest value from non-NULL values in column.
It is widely used for:

• Record dates
• Highest prices
• Top scores

What is MAX() Function
MAX() returns maximum non-NULL value in specified column or expression.
Ignores NULL values automatically.

Basic Syntax

sql

SELECT MAX(column_name) FROM table_name; 

Example

sql

SELECT MAX(marks) as highest_marks FROM Students; 

Returns highest mark scored by any student.

MAX with GROUP BY
Maximum per category.

sql

SELECT city, MAX(marks) as highest_city_mark 

FROM Students 

GROUP BY city; 

MAX with WHERE
Conditional maximums.

sql

SELECT MAX(marks) FROM Students 

WHERE status = 'Active'; 

MAX with Multiple Aggregates
Combine statistics.

sql

SELECT city, 

       MAX(marks) as highest, 

       MIN(marks) as lowest, 

       AVG(marks) as average 

FROM Students 

GROUP BY city; 

MAX with ORDER BY
Sort by maximum values.

sql

SELECT department, MAX(marks) as dept_highest 

FROM Students 

GROUP BY department 

ORDER BY dept_highest DESC; 

MAX with JOIN
Maximum across relationships.

sql

SELECT c.course_name, MAX(s.marks) as course_highest 

FROM Students s 

JOIN Courses c ON s.course_id = c.id 

GROUP BY c.id, c.course_name; 

MAX with HAVING
Filter groups by maximum.

sql

SELECT city, MAX(marks) as highest_mark 

FROM Students 

GROUP BY city 

HAVING MAX(marks) > 95; 

Multiple MAX Functions
Different criteria maximums.

sql

SELECT city,

       MAX(CASE WHEN status = 'Active' THEN marks END) as active_max,

       MAX(CASE WHEN marks > 0 THEN marks END) as valid_max

FROM Students

GROUP BY city;

MAX with Subquery
Find records matching maximum.

sql

SELECT name, marks 

FROM Students 

WHERE marks = (SELECT MAX(marks) FROM Students WHERE marks IS NOT NULL); 

MAX on Dates
Latest record.

sql

SELECT MAX(join_date) as latest_join FROM Students; 

MAX on Strings
Lexicographically largest.

sql

SELECT MAX(city) as last_city FROM Students; 

MAX with COALESCE
Handle all-NULL results.

sql

SELECT COALESCE(MAX(marks), 0) as safe_max FROM Students; 

Execution Order with MAX
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

• MAX very fast with indexes
• MAX(DISTINCT) slower
• Index columns used in MAX
• Works well with sorted data

Important Notes

• Ignores NULL values
• Empty set returns NULL
• Works with numbers, dates, strings
• String MAX uses collation order
• MAX(DISTINCT) removes duplicates first

Example Scenario
Top course performers:

sql

SELECT

    c.course_name,

    COUNT(s.id) as total_students,

    MAX(s.marks) as highest_score,

    MIN(s.marks) as lowest_score,

    ROUND(AVG(s.marks), 2) as average

FROM Courses c

LEFT JOIN Students s ON c.id = s.course_id

GROUP BY c.id, c.course_name

HAVING MAX(s.marks) > 90

ORDER BY highest_score DESC;

Common Mistakes

• MAX on non-indexed columns
• Surprised by NULL exclusion
• MAX(DISTINCT) performance overhead
• String collation affecting results
• Forgetting COALESCE for NULL results

Key Points to Remember

• MAX finds largest non-NULL value
• GROUP BY + MAX = category maximums
• Works with numbers, dates, strings
• Very fast with proper indexes
• Ignores NULLs automatically
• COALESCE(MAX(), default) for safety