MySQL MAX

MAX function finds a maximum value from a list of values. MySQL MAX Function is used to perform this aggregate operation on data columns of a MySQL data Table

This function accepts single column or expression as argument and returns single value as output. Any MySQL Datatype column or expression can be passed to MySQL MAX function.

If you need to find column wise maximum values then all those columns must be given in separate MAX functions in the SELECT statement.

Syntax of MySQL MAX Function

The MAX function is used in a SELECT query according to the given syntax.

MAX([DISTINCT]  expression|table_column)

  • MAX is the keyword used for in the MySQL SELECT statement.
  • DISTINCT is the keyword used in the query to compare only distinct values from column or expression. DISTINCT is optional keyword
  • table_column is the column name of the table from where the maximum value is to be found.
  • Expression is the expression using table column names and/or MySQL Arithmetic Operators.

Datatype of the argument to the MAX function is the datatype of the output value.

Examples of MySQL MAX

Finding maximum value in one column

The following query displays maximum billAmt.

SELECT MAX(billAmt) FROM sales;
MySQL Max example

Finding maximum values for multiple columns

This query displays MAX of columns billAmt and purAmt using separate MAX functions.

SELECT MAX(billAmt), MAX(purAmt) FROM sales;
multiple max values for different columns

Finding Maximum value in an expression

This query displays maximum values from columns billAmt  and purAmt.  Maximum value of expression to calculate profit percentage  is found with separate MAX function. You will observe that output of profit percentage of maximum values is different than maximum profit percentage of all the rows.

SELECT MAX(billAmt), MAX(purAmt),(MAX(billAmt)-MAX(purAmt))/MAX(purAmt)*100 AS "Profit percentage of max values",MAX(((billAmt-purAmt)/purAmt)*100) AS "Profit Percentage"  FROM sales;
MAX with expression

Using distinct keyword

To get maximum values from distinct column values, DISTINCT keyword is used. Two separate MAX functions are used for two columns.

SELECT MAX(DISTINCT billAmt), MAX(DISTINCT purAmt) FROM sales;
using DISTINCT keyword

Using where clause

To get MAX of column values that meets a specific condition, WHERE clause is used in the following query.

SELECT MAX(billAmt), MAX(purAmt) FROM sales WHERE sid='S1001';
SELECT MAX(billAmt), MAX(purAmt) FROM sales WHERE sid='S1002';

Finding maximum value from String and Date columns

This query is used to find alphabetically highest artist name and the maximum from DOB.

SELECT max(artist_name), MAX(dob) from artists;
MAX with string and date data type

Points to remember

  • You can use one MAX functions for a column name or expression. Separate MAX functions are needed for different columns or expressions.
  • MAX function can be used only in a SELECT statement.
  • DISTINCT keyword is used within parenthesis before column name or expression in the MAX function to find maximum value among distinct values. This doesn’t make any difference to the output.
  • WHERE clause is used to filter rows to find maximum values among specific values matching a specific condition.
  • MAX function returns output depending on the datatype of the  column or expression. If it is string datatype the output is alphabetically maximum value. From column or expression of DATE datatype the latest date is the output.
  • MAX function considers NULL values in columns as zeroes. It returns MAX of all the non-null values.