The largest value of two or more fields

Question

I need to get the largest value from two fields:

SELECT MAX(field1), MAX(field2) 

Now, how can I get the greatest value of these two?

Answer 1

You can use the GREATEST() function:

 SELECT GREATEST(field1, field2); 

If you want to get the absolute maximum of all rows, you can 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

If you selected GREATEST () for each row

 SELECT GREATEST(field1, field2) 

It will return NULL if one of the fields is NULL. You can use IFNULL to solve this problem.

 SELECT GREATEST(IFNULL(field1, 0), IFNULL(field2, 0)) 

Answer 3

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

So try:

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

Answer 4

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