Biggest value from two or more fields
MysqlMysql Problem Overview
I need to get the biggest value from two fields:
SELECT MAX(field1), MAX(field2)
Now, how can I get biggest value from these two?
Mysql Solutions
Solution 1 - Mysql
You may want to use the GREATEST()
function:
SELECT GREATEST(field1, field2);
If you want to get the absolute maximum from all the rows, then you may want to use the following:
SELECT GREATEST(MAX(field1), MAX(field2));
Example 1:
SELECT GREATEST(1, 2);
+----------------+
| GREATEST(1, 2) |
+----------------+
| 2 |
+----------------+
1 row in set (0.00 sec)
Example 2:
CREATE TABLE a (a int, b int);
INSERT INTO a VALUES (1, 1);
INSERT INTO a VALUES (2, 1);
INSERT INTO a VALUES (3, 1);
INSERT INTO a VALUES (1, 2);
INSERT INTO a VALUES (1, 4);
SELECT GREATEST(MAX(a), MAX(b)) FROM a;
+--------------------------+
| GREATEST(MAX(a), MAX(b)) |
+--------------------------+
| 4 |
+--------------------------+
1 row in set (0.02 sec)
Solution 2 - Mysql
In case you're selecting the GREATEST() for each row
SELECT GREATEST(field1, field2)
It will return NULL if one of the fields is NULL. You could use IFNULL to solve this
SELECT GREATEST(IFNULL(field1, 0), IFNULL(field2, 0))
Solution 3 - Mysql
mysql> SELECT GREATEST(2,0);
-> 2
So, try:
mysql> SELECT GREATEST(MAX(field1), MAX(field2));
Solution 4 - Mysql
Use of GREATEST/LEAST with MIN/MAX
GREATEST/LEAST: used with the columns, when you want to find the max or min value from the various columns.
MIN/MAX: used with the rows, when you want to find the max or min value from the various rows:
Example table:
SELECT GREATEST(col_a, col_b, col_c) FROM temp;
SELECT MIN(GREATEST(col_a, col_b, col_c)) FROM temp; # 3 as output
SELECT MAX(GREATEST(col_a, col_b, col_c)) FROM temp; # 9 as output
SELECT LEAST(col_a, col_b, col_c) FROM temp;
SELECT MIN(LEAST(col_a, col_b, col_c)) FROM temp; # 1 as output
SELECT MAX(LEAST(col_a, col_b, col_c)) FROM temp; # 7 as output
Solution 5 - Mysql
SELECT max( CASE
WHEN field1 > field2 THEN field1
ELSE field2
END ) as biggestvalue
FROM YourTable;