I am trying to fetch the minimum value of a column in MySQL using the MIN
function. But is it possible to tell MySQL to ignore the zero values? Problem is that I am storing 0
as default value instead of NULL
for a tinyint
column. What I want is to get the minimum value that is greater than 0.
SELECT a.baseloc_id, a.baseloc_latitude, a.baseloc_longitude, a.baseloc_distance, MIN(b.basecost_ton2_cost) as minTon2, MIN(b.basecost_ton3_cost) as minTon3, MIN(b.basecost_ton10_cost) as minTon10 FROM bbox_logi_base_locations a LEFT JOIN bbox_logi_base_cost b USING (baseloc_id) GROUP BY a.baseloc_id;
Thank you for any help.
Sorry that I forgot to mention this. The bbox_logi_base_cost
table has rows that contain fragmented values. For example, one row can have basecost_ton2_cost
as 0
but other columns filled with values. And the other row can have every column but one as 0
. So no row can be filtered using a WHERE
condition.
Use this:
MIN(NULLIF(value, 0))
The correct answer for you is:
IFNULL(MIN(NULLIF(value, 0)), 0)
While ISNULL
not working.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With