00 Votes

MySQL: Determine the larger of two values

Question by NetLabel | 2012-01-22 at 19:06

I have a little problem with MySQL. I want to find the larger of two values and return it. I tried it with MAX(), but that only leads to an error message. What can I do?

So far, my query looks like this: SELECT MAX(0, (SELECT ...)). Thus, it should be looked up, if 0 or the result of the MySQL SELECT statement is larger, and the larger value of these two values should be displayed.

ReplyPositiveNegative
Avatar
0Best Answer0 Votes

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

ReplyPositive Negative
Reply

Related Topics

MySQL: Calculate Average

Question | 1 Answer

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.