Mysql max value from 3 different columns

Question

Is there any way to find maximum value of 3 different columns? I'm trying to find records with any of 3 columns value higher than specified value and trying to avoid making something like this in query:

column1 > 69 or column2 > 69 or column3 > 69

Table structure is like this:

id | column1 | column2 | column3
1  |   5     |    4    |    3
2  |  70     |    1    |    65
3  |  66     |    3    |    90

And select like this:

select id from tablex where column1 > 69 or column2 > 69 or column3 > 69

-- but with better query, a bit prettier like this (it doesn't work of course)

select id from tablex where MAX(column1, column2, column3) > 69

Solution 1

you need to use GREATEST

like that

    select id from tablex where GREATEST(column1, column2, column3) > 69

Solution 2

I want to point out that:

where GREATEST(column1, column2, column3) > 69;

is not the same as:

where column1 > 69 or column2 > 69 or column3 > 69;

The first will filter out all rows where any of the three columns is NULL. The second will still consider these rows. You could rewrite the GREATEST() query as:

where GREATEST(coalesce(column1, 0), coalesce(column2, 0), coalesce(column3, 0)) > 69;

but that defeats the purpose of the simpler syntax. You may know that the column values are never NULL, in which case using greatest() is ok. But, it is not a general substitute.

Comments

[J33nn]

True... I came to this answer like in same time :) Thanks!

[Leigh]

Good spot. I had not realized greatest included nulls. (Makes complete sense though.)