How to get the minimum value of a mysql-table column based on the number sequence in a row

I have a mysql-table (results) with six columns like this:

+----+-------+-------+-------+-------+-------+
| id | can_1 | can_2 | can_3 | can_4 | can_5 |
+----+-------+-------+-------+-------+-------+
|  1 |     1 |     0 |     2 |     4 |     3 |
+----+-------+-------+-------+-------+-------+
|  2 |     2 |     1 |     5 |     3 |     4 |
+----+-------+-------+-------+-------+-------+
|  3 |     3 |     1 |     0 |     0 |     0 |
+----+-------+-------+-------+-------+-------+
|  4 |     0 |     2 |     1 |     0 |     3 |
+----+-------+-------+-------+-------+-------+

I am trying to grab rows with the least value larger than 1 from the can_1 column based on what ever values are stored in each row.

For example: can_1 needs to return rows (id) 2 and 3, since 2 is the min value on row 2 and 3 is the min value of row 3.

Using

SELECT id FROM results WHERE can_1 = (SELECT MIN(can_1) FROM results);

returns

+----+
| id |
+----+
|  2 |
+----+

This is understandable - the MIN-value checks for the minimum value larger than 1 in the entire column (can_1), which is 2 in the can_1 case. But how do I combine this so the minimum value of the entire row (covering all columns) determines what minimum values to return for the can_1 column, ie. for the result to be this?

+----+
| id |
+----+
|  2 |
+----+
|  3 |
+----+

Solution

You can do it with the function LEAST():

select id
from tablename
where can_1 > 1
and can_1 = least(
  can_1, 
  case when can_2 <= 1 then can_1 + 1 else can_2 end, 
  case when can_3 <= 1 then can_1 + 1 else can_3 end, 
  case when can_4 <= 1 then can_1 + 1 else can_4 end, 
  case when can_5 <= 1 then can_1 + 1 else can_5 end
)

See the demo.
Results:

| id  |
| --- |
| 2   |
| 3   |