How to get min value from a single row in mysql

Question

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.

BTW: excpected result is: 2 – Eyal Sooliman Mar 18 '16 at 21:26 — Eyal Sooliman, Mar 18 '16 at 21:26
Well, there's no id here. That aside, see normalisation – Strawberry Mar 18 '16 at 22:09 — Strawberry, Mar 18 '16 at 22:09

score 2 · Answer 1 · answered Mar 18 '16 at 21:27

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;

1 Answers1