MySQL - Find MIN but not zero

Question

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.

can u upload the query ?

Haim Evgi
– Haim Evgi

2010-01-20 07:39:52 +00:00
Commented Jan 20, 2010 at 7:39 — Haim Evgi
– Haim Evgi, Commented Jan 20, 2010 at 7:39
@haim evgi - I have uploaded the query. Please have a look.

Nirmal
– Nirmal

2010-01-20 07:40:58 +00:00
Commented Jan 20, 2010 at 7:40 — Nirmal
– Nirmal, Commented Jan 20, 2010 at 7:40

David M · Accepted Answer · 2010-01-20 07:40:40Z

95

Use this:

MIN(NULLIF(value, 0))

answered Jan 20, 2010 at 7:40

David M

73.2k13 gold badges164 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Nirmal Over a year ago

@David: That's beautiful! Solved the issue. Good creative use of NULLIF!

Priyanshu Over a year ago

Hey Hi, It returns Null if highest value is 0, but What if I want to select 0 if no higher value than zero is present in column, and when there is higher value than zero is present i want that as minimum.

Alphacoder Over a year ago

very nice solution!!

Lionel Pire Over a year ago

@Priyanshu ISNULL(MIN(NULLIF(value, 0)), 0) could do the trick

double-beep · Accepted Answer · 2019-02-19 18:32:14Z

6

The correct answer for you is:

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

While ISNULL not working.

edited Feb 19, 2019 at 18:32

double-beep

5,67019 gold badges43 silver badges50 bronze badges

answered Feb 19, 2019 at 16:27

Ninet9

1833 silver badges11 bronze badges

Comments

SOURAV KARAK · Accepted Answer · 2021-02-18 10:45:46Z

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

bigtech · Accepted Answer · 2024-11-13 22:43:52Z

0

If you're all hung up on brevity, then this answer will suffice:

IFNULL(MIN(ID), 0)

answered Nov 13, 2024 at 22:43

bigtech

4744 silver badges6 bronze badges

Collectives™ on Stack Overflow

MySQL - Find MIN but not zero

EDIT 01:

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT 01:

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related