Looking for the lowest or biggest value in multiple columns in a mysql table? There are two handy function in mysql, namely LEAST and GREATEST, which return the lowest / biggest value from the given list of parameters. Therefore, if you like to get the absolute minimum or maximum in the whole table over multiple rows, you could combine these two functions with MIN or MAX and use the following SQL statement:
SELECT MIN(LEAST(column1, column2, column3, column...)) FROM table1
But LEAST and GREATEST have some limitations concerning their handling of NULL values:
- GREATEST returns NULL if any of the values is NULL
- LEAST returns NULL if all values are NULL
The problem in my case was, that some columns in my table were not required and had to stay NULL and my statement – especially for retrieving the absolute maximum – failed to deviler the proper values. Searching for a solution was quite tedious, so I thought to share it, since it turns out to be pretty simple – just turn it around:
SELECT LEAST(MIN(column1), MIN(column2), MIN(column3), ...)) FROM table1
To handle null we can use query as below
SELECT LEAST(MIN(column1), MIN(column2), MIN(column3), …)) column FROM table1 having column IS NOT NULL
Hope it will solve your problem
Thanks for your post.
But how can you handle the null values when for example the min(col1) is null?
@Guillermo: check out the discussion on stackoverflow. Don’t know which kind of problem u got with NULL, but both ways are answered there.