MySQL - Find MIN but not zero

user_137954 0
Viewed 29401

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.

EDIT 01:

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.

mysql
3 Answers

Use this:

MIN(NULLIF(value, 0))
user_831090

The correct answer for you is:

IFNULL(MIN(NULLIF(value, 0)), 0)

While ISNULL not working.

user_22241000

A quick note to help me remember how to do this.

The problem: you want to select the smallest value from a set of values.

Let's say you have a table of products that are in a logical group and you want to select the lowest priced product from that group, however some products actually have a 0.00 price (for whatever reason). You don't want to actually show 0.00 as the lowest price for this group of products, you want to show the lowest price that happens to be greater than zero.

MySQL has a neat way to do this. Simply go:

SELECT tableref.group_id, MIN(NULLIF(tableref.column, 0)) as min_price FROM tableref GROUP BY tableref.group_id;

The magic is in the NULLIF function, which will return null if tableref.column is equal to 0. Returning null removes that value from inclusion by MIN, having the effect of forcing the column value to be greater than zero.

#MySQL #Database

user_105664600
Related