MySQL: how to pick the smallest integer that isn't a zero

Question

I feel like this would be easy but it's proving harder than it should.

Currently, I have several price fields for each record in a table (sale_price, retail_price, discounted_price, other_price) but I would like to get only the highest and lowest non-zero values.

This works fine for highest value:

SELECT GREATEST(retail_price, sale_price, discounted_price, other_price) AS highest_price FROM TABLE

However, fields in the database that don't have values are stored as zeros so finding the smallest value that isn't a zero is proving challenging. This doesn't work:

SELECT LEAST(retail_price, sale_price, discounted_price, other_price) AS lowest_price FROM TABLE

as it returns a zero.

Is there an easy way to grab a non-zero smallest value from a list with a simple modification to the statement?

Any help/nudge in the right direction is greatly appreciated!

Example data:

stock_number    discounted_price    sale_price      retail_price    other_price
1               999                 888             0               777
2               55                  22              33              11
3               0                   0               0               0

What the ideal filtered result should be:

stock_number    highest_price       lowest_price
1               999                 777
2               55                  11
3               0                   0

Answer 1

You can use an IF condition to return the highest possible integer if the number is equal to zero. I used modulo instead of an additional IF for IF output is ~0>>32 return 0.

SELECT *, LEAST(IF(retail_price>0,retail_price,~0>>32),
 IF(sale_price>0,sale_price,~0>>32), 
 IF(discounted_price>0,discounted_price,~0>>32), 
 IF(other_price>0,other_price,~0>>32))%(~0>>32)
AS lowest_price FROM table_name;


+--------------+------------+------------------+-------------+--------------+
| retail_price | sale_price | discounted_price | other_price | lowest_price |
+--------------+------------+------------------+-------------+--------------+
|       434.28 |    6992.52 |          8969.79 |    11526.37 |       434.28 |
|      6032.47 |    7928.27 |          9198.91 |     9864.75 |      6032.47 |
|      9382.03 |    4970.88 |          9053.33 |     5664.02 |      4970.88 |
|      1160.12 |    1153.51 |          2287.21 |     7975.51 |      1153.51 |
|      8325.92 |    5358.05 |          1812.52 |     5333.46 |      1812.52 |
|      8884.71 |    3733.20 |          4356.84 |    10584.62 |      3733.20 |
|      2817.57 |    7023.99 |          1977.24 |     1159.22 |      1159.22 |
|     12209.40 |    8189.34 |          4318.52 |     9369.54 |      4318.52 |
|      9202.18 |    5557.26 |           179.77 |     8917.08 |       179.77 |
|         0.00 |       1.00 |             2.00 |        3.00 |         1.00 |
|       100.00 |     201.00 |           302.00 |        0.00 |       100.00 |
|         0.00 |       0.00 |             0.00 |        0.00 |         0.00 |
+--------------+------------+------------------+-------------+--------------+
12 rows in set (0.00 sec)