How to get the max of two values in MySQL?
Asked Answered
U

4

356

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
Ultraviolet answered 14/10, 2009 at 11:25 Comment(0)
V
652

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)

Visional answered 14/10, 2009 at 11:29 Comment(7)
example: select greatest(queue.count - 1, 0) from queue;Leanneleanor
one thing need to keep in mind that whenever if any single value contains null at that time this function always returns null as answer!Veda
There is also LEASTToreador
how can i run a sub query as a parameter to GREATEST so that i can get values for a cirtain columnIpoh
To prevent the problem with null you can use ifnull. E.g. select greatest(date1, ifnull(date2, "0000-00-00 00.00:00")) from table1 where date2 is null; will get you date1.Colchicum
sadly, if you are using LEAST, and you put something like SELECT LEAST(20000, COUNT(*)) FROM massive_table WHERE index_breaking_filter; it can't shortcut the logic after the COUNT(*) exceeds 20k - it takes just as long as the query without the least.Arnie
If some values may be null, you can do GREATEST(COALESCE(column1, 0), COALESCE(column2, 0))Baleful
O
33

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
Oe answered 13/10, 2014 at 18:7 Comment(1)
Watch out for null values with GREATEST. Any null value will cause the function to return null. To prevent this, you can do GREATEST(COALESCE(column1, 0), COALESCE(column2, 0))Baleful
C
8

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).

Conduplicate answered 9/7, 2016 at 0:16 Comment(3)
This is why I honestly hate MySQL. What a tremendous amount of work to do such a simple thing.Blaze
This can be simply replaced with select COALESCE(GREATEST(fieldA, fieldB),fieldA,fieldB) as maxValue. It is overcomplicatedLaforge
If some values can be null, you can do GREATEST(COALESCE(column1, 0), COALESCE(column2, 0))Baleful
T
0

SELECT GREATEST(value1, value2) AS maxvalues;

Traherne answered 21/3, 2024 at 10:43 Comment(0)

© 2022 - 2025 — McMap. All rights reserved.