MySQL MAX Function

MySQL MAX function returns the highest value in the specified column. Specified column should have numeric data type; MAX function does not accept columns having a data type other than numeric, such as character or date. NULL values are ignored when using the MAX function. The DISTINCT command is an option. However, because the maximum value for all the rows is the same as the distinct maximum value, DISTINCT is useless.

The syntax of the MAX function is as follows:

SELECT MAX(<column-name>) FROM TABLE_NAME;

There should be no space between MAX and left Brackets (Parentheses), otherwise it is an error.

MySQL MAX Function Examples

Creating table for demonstrating MAX function

CREATE TABLE EMP_SALARY_COMMISION

(EMP_ID numeric(4) ,

SALARY numeric(7,2),

COMM numeric(7,2)

);

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('100', '1000', '110');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('101', '1000', '100');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('102', '1000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('103', '1000', '20');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('104', '2000', '100');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('105', '2000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('106', '2000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('107', '3000', '60');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('108', '3000', '60');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('109', '3000');

COMMIT;

MAX of all rows

SELECT MAX(COMM) FROM EMP_SALARY_COMMISION; --------- 110

MAX of distinct rows

SELECT MAX(DISTINCT COMM) FROM EMP_SALARY_COMMISION; --------- 110

Adding alias to MAX value

SELECT MAX(SALARY) AS MAX_SALARY FROM EMP_SALARY_COMMISION;

All Chapters