MySQL - biggest value from two fields

Question

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?

Answer 1

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)

Answer 2

SELECT max( CASE WHEN field1 > field2 THEN field1 ELSE field2 END ) as biggestvalue from YourTable

Answer 3

mysql> SELECT GREATEST(2,0);
        -> 2

So, try:

mysql> SELECT GREATEST(MAX(field1), MAX(field2));