Return Least Value Greater than Zero from Same Row in SQL

I want to get the least of prices from different suppliers and the name of the supplier. The least should be greater than zero(0). I have been able to get minimum value if zero is not in the list. For instance, based on the code below, i want to be able to select 17 as minimum price instead of 0 and sup3 as the supplier. see my code below:

SELECT least(sup1,sup2,sup3,sup4,sup5) AS mininumValue,
       (CASE least(sup1, sup2, sup3, sup4, sup5)
            WHEN sup1 THEN 'sup1'
            WHEN sup2 THEN 'sup2'
            WHEN sup3 THEN 'sup3'
            WHEN sup4 THEN 'sup4'
            WHEN sup5 THEN 'sup5'
        END) as Supplier 
FROM price_charts  
WHERE partno = 'OL0002';

Answer

This is somewhat painful in MySQL. One method is to unpivot the data, filter and aggregate. Another is to do some tricks.

Unfortunately, LEAST() returns NULL if any value is NULL. But you can use a magic value:

select least(coalesce(nullif(sup1, 0), 999999),
             coalesce(nullif(sup2, 0), 999999),
             coalesce(nullif(sup3, 0), 999999),
             coalesce(nullif(sup4, 0), 999999),
             coalesce(nullif(sup5, 0), 999999)
            ) as minimumValue,
       (case least(coalesce(nullif(sup1, 0), 999999),
                   coalesce(nullif(sup2, 0), 999999),
                   coalesce(nullif(sup3, 0), 999999),
                   coalesce(nullif(sup4, 0), 999999),
                   coalesce(nullif(sup5, 0), 999999)
                  )
            when sup1 then 'sup1'
            when sup2 then 'sup2'
            when sup3 then 'sup3'
            when sup4 then 'sup4'
            when sup5 then 'sup5'
       end) as Supplier 
from price_charts pc 
where partno = 'OL0002';

Note: You have a poor data model. You should have another table with one row per supplier and price, not pivoting the values into separate columns.