get the least / greatest from multiple columns in mysql 5.x

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

2 Responses to 'get the least / greatest from multiple columns in mysql 5.x'

  1. Guillermo sagt:

    Thanks for your post.
    But how can you handle the null values when for example the min(col1) is null?

Leave a Reply

Your email address will not be published. Required fields are marked *

*


*