MySQL - Finding the smallest value that is not zero

492 views mysql
4

Sample table

----------------------------------------
Fruit           Store1           Store2
Apple             5                 6
Banana            10                4
Oranges           1                 1
Avocado           2                 1
----------------------------------------

Apparently, SELECT MIN(NULLIF(Store1, 0)) FROM table only gets the value and not the row where it is contained. I also need to compare between the two stores and get the store name.

This is my target output per queue of an item

----------------------------
Fruit      Lowest     Source
Apple        5        Store1
----------------------------

----------------------------
Fruit      Lowest     Source
Banana       4        Store2
----------------------------

----------------------------
Fruit      Lowest     Source
Oranges      1        Store1
----------------------------

----------------------------
Fruit      Lowest     Source
Avocado      1        Store2
----------------------------

answered question

What if there's a store3?

1 Answer

0

You can use Conditional comparison functions, such as Least() and Case .. When.

Always remember that Min() function is used to find minimum value out of different row values in a column. Here, you are trying to find minimum value out of multiple column(s). For that, we will use Least() function.

SELECT 
  Fruit, 
  LEAST(Store1, Store2) AS Lowest, 
  (CASE WHEN Store1 < Store2 THEN 'Store1' ELSE 'Store2' END) AS Source
FROM your_table 

posted this

Have an answer?

JD

Please login first before posting an answer.