Biggest value from two or more 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)