How to get min value from a single row in mysql

问题:

I would like to know how to get the min numeric value from a single row, for example:

My table:

|col a|col b|col c|some other col|some other col|
-------------------------------------------------
|  13 |  2  |  5  |     0        |       0      |

The question is: how do I get the min value from col a, col b and col c (I want to ignore the other columns.

I have try this: SELECT MIN(col a, col b, col c) from table WHERE id=0

But surly this doesn't work.

Any help will be appreciated.

回答1:

The correct function in MySQL is least():

SELECT least(cola, colb, colc) 
FROM table
WHERE id = 0;

Very important: This assumes that all values are non-NULL. If you have NULL values, then least() returns NULL.

If you need to take this into account, you can use coalesce() . . . One method is to use a highest value:

SELECT nullif(least(coalesce(cola, 999999), coalesce(colb, 999999), coalesce(colc, 999999)), 999999)
FROM table
WHERE id = 0;

Or, use a coalesce() on the values in the columns:

SELECT least(coalesce(cola, colb, colc), coalesce(colb, colc, cola), coalesce(colc, colb, cola))
FROM table
WHERE id = 0;