MySQL: how to choose the smallest integer that is not zero

Question

It seems to me that this would be easy, but it turned out to be more difficult than necessary.

Currently, I have several price fields for each record in the 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 the highest value:

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

However, fields in the database that do not have values ​​are stored as zeros, so finding the smallest value that is not zero proves complexity. This does not work:

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

when it returns zero.

Is there an easy way to grab a nonzero smallest value from a list with a simple modification to the instruction?

Any help / push in the right direction is appreciated!

Sample 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

:

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

Answer 1

IF, , . modulo IF 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)

Answer 2

SELECT GREATEST(discounted_price,sale_price,retail_price,other_price)
as highest_price,
LEAST(discounted_price,sale_price,retail_price,other_price) as lowest_price
FROM TABLE
WHERE retail_price > 0 AND sale_price > 0;

Answer 3

, SQL - , , . :

SELECT
  LEAST(
    IF(!retail_price,
       0,
       retail_price - GREATEST(retail_price, sale_price,
                               discounted_price, other_price)),
    IF(!sale_price,
       0,
       sale_price - GREATEST(retail_price, sale_price,
                             discounted_price, other_price)),
    IF(!discounted_price,
       0,
       discounted_price - GREATEST(retail_price, sale_price,
                                   discounted_price, other_price)),
    IF(!other_price,
       0,
       other_price - GREATEST(retail_price, sale_price,
                              discounted_price, other_price))
  )
  + GREATEST(retail_price, sale_price, discounted_price, other_price)
  AS lowest_price
FROM
  `TABLE`

:

• : 0, 0, -

0, 0 s. , , <= 0, .

, :

(2, 4, 0, 8)

8 , 0 s,

(-6, -4, 0, 0)

, 0 , , LEAST . LEAST , GREATEST, .

, , .

, 0, 0. , , 0, , .