How to get the max of two values in MySQL?

The Question :

301 people think this question is useful

I tried but failed:

mysql> select max(1,0);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use 
near '0)' at line 1
The Question Comments :

The Answer 1

547 people think this answer is useful

Use GREATEST()

E.g.:

SELECT GREATEST(2,1);

Note: Whenever if any single value contains null at that time this function always returns null (Thanks to user @sanghavi7)

The Answer 2

27 people think this answer is useful

To get the maximum value of a column across a set of rows:

SELECT MAX(column1) FROM table; -- expect one result

To get the maximum value of a set of columns, literals, or variables for each row:

SELECT GREATEST(column1, 1, 0, @val) FROM table; -- expect many results

The Answer 3

6 people think this answer is useful

You can use GREATEST function with not nullable fields. If one of this values (or both) can be NULL, don’t use it (result can be NULL).

select 
    if(
        fieldA is NULL, 
        if(fieldB is NULL, NULL, fieldB), /* second NULL is default value */
        if(fieldB is NULL, field A, GREATEST(fieldA, fieldB))
    ) as maxValue

You can change NULL to your preferred default value (if both values is NULL).

Tags:,

Add a Comment