CODEWITHSUNDEEP
mysqlsqlsql-null
Solal
Solal

Reputation: 751

SELECT least value between two columns with MYSQL

Hello I am trying to select the smallest value between 3 columns with MySQL.

The issue I am facing is that in a column I might have NULL value. How can I get the least value that is not NULL?

Running select least(1, 3, NULL) will return NULL but I would like to have 1 as an answer.

Thank you for your help.

Upvotes: 1

Views: 44

Answers (1)

GMB
GMB

Reputation: 222432

The way least() handles nulls is a documented behavior:

If any argument is NULL, the result is NULL. No comparison is needed.

That might seem annoying, but propagating the null value to the resultset is how your database signals you that one of your values is undefined; this is consistent with the behavior of other operations that involve null (string concatenation, arithmetic operations, ...).

If only the third column may be null, and none of the other two, you could do:

least(col1, col2, coalesce(col3, col1))

Upvotes: 3

Related Questions