Biggest value from two or more fields

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?

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

Solution 2:

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> SELECT GREATEST(2,0);
        -> 2

So, try:

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

Solution 4:

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