MySQL query help

Question

I have a table with the following structure

id(int) | col1(int) | col2(int) | col3(int)
------------------------------------------
1       | NULL      | 10        | 20
2       | 5         | NULL      | 30
3       | 8         | NULL      | NULL

Given an 'id' value I need to find the minimum non-null value across all the columns in that record.
Like for example, for id=1, the value is 10. For id =2 the value is 5 and so on.

How do I do this in MySQL?

Any help would be greatly appreciated!

Answer 1

Been a while since I used them, but I think you can use MySQL's MIN and LEAST functions like so:

SELECT MIN(LEAST(col1,col2,col3)) FROM table WHERE id=1;

Answer 2

Give this a shot:

SELECT id, LEAST(
   ifnull(ifnull(col1, col2), col3),
   ifnull(ifnull(col2, col1), col3),
   ifnull(ifnull(col3, col1), col2)) FROM table;

Answer 3

LEAST() would be best if it was NULL safe, but since it isn't, this is the cleanest solution i can think of:

SELECT MIN(cols)
  FROM (SELECT col1
          FROM table
         WHERE id = 1
         UNION
        SELECT col2
          FROM table
         WHERE id = 1
         UNION col3
          FROM table
         WHERE id = 1) AS dt

if you need it in one line, this is the best i can think of:

SELECT LEAST(COALESCE(col1, col2, col3)
           , COALESCE(col2, col3, col1)
           , COALESCE(col3, col1, col2))
  FROM table
 WHERE id = 1

Answer 4

SELECT  LEAST(
        COALESCE(col1, col2, col3),
        COALESCE(col2, col1, col3),
        COALESCE(col3, col1, col2)
        )
FROM    mytable

Alternatively, use session variables:

SELECT  LEAST(
        @r := COALESCE(col1, col2, col3),
        @r := COALESCE(@r, col2),
        @r := COALESCE(@r, col3)
        )
FROM    mytable