mysql取行id最小的行,如何从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.

解决方案

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;