sql min() for rows?

[dialgarocksful]

Hi, I have this dilemma here. I'd like to know if using min() is viable for rows. Am trying to get the lowest price that isn't a zero per item

 

EDIT: I need to call the column_name with matching price from store1/store2/store3.

 

[dialgarocksful]

8 minutes ago, Pangea2017 said:

why not redesign the table?

just three rows: item_name, store, price

just use a normal search and you don't need to save any values for stores which don't have the item.

it's more of a price comparison tool to get the cheapest possible price (much like pcpartpicker's). i still need the original prices of items per store

[dialgarocksful]

3 minutes ago, Pangea2017 said:

you still have it. The table would look like this:

item_name	store_ID	price
a	1	100
a	2	200
a	3	150
b	2	1000
c	1	500
c	3	700

I think I got it using least() and having a where clause that the price > 0.

 

Next dilemma is getting the name of the column (from the original table where the price is equal to the answer of the least query ).