MySQL: Determine the larger of two values

You can use MAX() and MIN() only in order to determine the largest value from a column in MySQL, so, for example like it is demonstrated in the following example:

SELECT MIN(points), MAX(points) FROM tab;

Here, we get from the table "tab" the minimum and the maximum value, which occurs in the column "points".

What you need is GREATEST(). GREATEST() returns the largest value of two or more values. The counterpart to this is LEAST(), which gives us the minimum value:

SELECT GREATEST(1,8,3)
-> 8
SELECT LEAST(1,8,3)
-> 1

In this context, you should ask yourself, whether one of the selected values may be NULL. Until MySQL 4.1, GREATEST returns NULL only if all compared values are NULL. From MySQL 5.1, NULL is returned when only one argument is null, so:

SELECT GREATEST(NULL, 8)
-> NULL from MySQL 5.1
SELECT COALESCE(NULL, 8)
-> 8

Here, you can work, for example, with COALESCE(). COALESCE() will return the first value in the list that is not NULL or NULL if all values in the list are NULL.
2012-01-22 at 23:27