CODEWITHSUNDEEP
mysqlsql
usii
usii

Reputation: 1113

How to implement least in mysql query without count 0?

I want to implement least in mysql query but least should not be count 0 how can i do that?

this is my query

    LEAST(price, price_double, price_triple, price_quad) AS minvalue

it is counting 0 right now but i dont want to count 0 and search the result with out having zero

For example

   price            100
   price_double     75
   price_tripple    50
   price_quad       0

the minvalue will be 50 not 0, but right now it is including 0.

Please help me to do that, i m in very trouble, i search a lot but didn't get any success, thanks a million ton thanks in advance.

Upvotes: 2

Views: 176

Answers (3)

bitWorking
bitWorking

Reputation: 12655

This is the same like Gordon Linoff's solution only a little shorter:

LEAST
(
    IF(price > 0, price, 999999),
    IF(price_double > 0, price_double, 999999),
    IF(price_triple > 0, price_triple, 999999),
    IF(price_quad > 0, price_quad, 999999)
)

Upvotes: 1

Sudz
Sudz

Reputation: 4308

Use where clause

 SELECT LEAST(price, price_double, price_triple, price_quad)
 FROM ....
 WHERE LEAST(price, price_double, price_triple, price_quad) > 0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use case in the least() function to do this. Unfortunately, least() returns NULL if any arguments are NULL in the more recent versions of MySQL. So, this uses a dummy value that is big:

least(case when price > 0 then price else 999999 end,
      case when price_double > 0 then price_double else 999999 end,
      case when price_triple > 0 then price_triple else 999999  end,
      case when price_quad > 0 then price_quad else 999999 end
     )

Upvotes: 1

Related Questions