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
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
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)
LEAST
–
Toreador GREATEST
so that i can get values for a cirtain column –
Ipoh select greatest(date1, ifnull(date2, "0000-00-00 00.00:00")) from table1 where date2 is null;
will get you date1. –
Colchicum 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 GREATEST(COALESCE(column1, 0), COALESCE(column2, 0))
–
Baleful 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
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 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).
select COALESCE(GREATEST(fieldA, fieldB),fieldA,fieldB) as maxValue
. It is overcomplicated –
Laforge GREATEST(COALESCE(column1, 0), COALESCE(column2, 0))
–
Baleful © 2022 - 2025 — McMap. All rights reserved.