MAX() in SQL

📈 The MAX() function is an aggregate function in SQL that returns the largest value from a specified column. It works with numeric values, dates, and text data. The MAX()function is commonly used to find the highest salary, maximum score, latest date, greatest value, and other maximum values in a dataset.

📖 What is MAX()?

The MAX() function scans all selected values in a column and returns the largest one. It automatically ignores NULL values during the calculation.

Information

MAX() works with numeric, date, and character data types. For character data, the largest value is determined according to the database's sorting and collation rules.

📝 Basic Syntax

MAX() Syntax

SELECT MAX(column_name)
FROM table_name
WHERE condition;

📊 Sample Table

Consider the following Students table:

StudentIDNameDepartmentMarksAdmissionDate
101AliceComputer Science922024-01-15
102BobMathematics852024-02-10
103CharliePhysics782024-03-20
104DavidComputer Science952024-04-05
105EvaMathematics882024-05-12

1ī¸âƒŖ Finding the Maximum Numeric Value

Retrieve the highest marks obtained by any student.

Maximum Marks

SELECT MAX(Marks) AS HighestMarks
FROM Students;

Result:

HighestMarks
95

2ī¸âƒŖ MAX() with WHERE

Use the WHERE clause to find the maximum value from filtered records.

Highest Marks in Computer Science

SELECT MAX(Marks) AS HighestCSMarks
FROM Students
WHERE Department = 'Computer Science';

3ī¸âƒŖ MAX() with Dates

The MAX() function can return the latest date in a column.

Latest Admission Date

SELECT MAX(AdmissionDate) AS LatestAdmission
FROM Students;

4ī¸âƒŖ MAX() with Text

The MAX() function also works with character data. The largest value is determined according to the database's collation and sorting rules.

Alphabetically Last Name

SELECT MAX(Name) AS LastName
FROM Students;

Important

Character comparisons depend on the database's collation settings. Different collations may produce different ordering results.

📊 MAX() with GROUP BY

The GROUP BY clause groups rows, and MAX() returns the largest value for each group.

Highest Marks by Department

SELECT Department,
       MAX(Marks) AS HighestMarks
FROM Students
GROUP BY Department;

Sample Result:

DepartmentHighestMarks
Computer Science95
Mathematics88
Physics78

📈 MAX() with HAVING

The HAVING clause filters grouped results after the maximum values have been calculated.

Departments with Maximum Marks Above 90

SELECT Department,
       MAX(Marks) AS HighestMarks
FROM Students
GROUP BY Department
HAVING MAX(Marks) > 90;

🔗 MAX() with JOIN

The MAX() function is frequently used with joins to determine the highest value across related tables.

Highest Course Score per Student

SELECT s.Name,
       MAX(c.Score) AS HighestScore
FROM Students s
JOIN Courses c
ON s.StudentID = c.StudentID
GROUP BY s.Name;

⚠ī¸ MAX() and NULL Values

The MAX() function ignores NULL values. Only valid values are considered when determining the maximum.

Column ValuesMAX() Result
10, 20, NULL, 3030

Important

If every value in the selected column is NULL, MAX() returns NULL.

📊 Common Uses of MAX()

PurposeExample
Highest Marks MAX(Marks)
Highest Salary MAX(Salary)
Highest Price MAX(Price)
Latest Date MAX(OrderDate)
Department-wise Maximum MAX(...) GROUP BY Department

⚖ī¸ MAX() vs MIN()

FunctionPurpose
MAX()Returns the largest value.
MIN()Returns the smallest value.

đŸ’ŧ Real-World Example

A university administrator wants to identify the highest marks achieved in each department.

Department-wise Maximum Marks

SELECT Department,
       MAX(Marks) AS HighestMarks
FROM Students
GROUP BY Department
ORDER BY HighestMarks DESC;

This query generates a report showing the highest marks for every department, sorted from the highest value to the lowest.

⚠ī¸ Best Practices

Best Practice

Use MAX() to retrieve the largest value in a dataset, combine it with WHERE for filtered searches, and use GROUP BY to find maximum values within groups. Remember that MAX() ignores NULL values and that character comparisons depend on the database's collation settings.

🚀 Key Points to Remember

  • 📌 MAX() returns the largest value in a column.
  • 📌 It works with numeric, date, and text data types.
  • 📌 It ignores NULL values.
  • 📌 It is commonly used with WHERE, GROUP BY, and HAVING.
  • 📌 It can be combined with joins for advanced queries.
  • 📌 Text results depend on the database's collation and sorting rules.
>>"The MAX() function quickly identifies the largest value in your data, making it indispensable for reporting and analysis."

Summary

✅ The MAX() function is an essential SQL aggregate function for finding the largest value in numeric, date, or text columns. Whether identifying the highest marks, latest dates, or maximum prices, it provides valuable insights for reporting, analytics, and data-driven decision-making.