Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL - Find MIN but not zero

Tags:

mysql

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.

like image 523
Nirmal Avatar asked Jan 20 '10 07:01

Nirmal


2 Answers

Use this:

MIN(NULLIF(value, 0)) 
like image 194
David M Avatar answered Sep 20 '22 20:09

David M


The correct answer for you is:

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

While ISNULL not working.

like image 26
Ninet9 Avatar answered Sep 21 '22 20:09

Ninet9