SQL query for the store with the minimum price of an item in the city

Question

I have this table

(city,storeID,itemID,price)

I need to return for each city,itemID the storeID with the minimum price for the item and the minimum price itself ( city,itemID,storeIDmin,minprice).

Can someone help me with this query ?

Thanks!

Answer 1

I solved this with Join and Subquery (Also possible to use "WITH AS" Clause if you work on oracle DB):

SELECT table1.city, table1.itemID, table1.storeID as storeIDmin, subquery.min_price 
FROM table1
JOIN (select city, itemID, min(price) as min_price from table1 
group by city,itemID) AS subquery
ON table1.city = subquery.city
AND table1.itemID = subqueryitemID
AND table1.price = 
subquery.min_price

the result for example:

+------+---------+--------+-------+
| city | storeID | itemID | price |
+------+---------+--------+-------+
|    1 |       1 |      1 |    70 |
|    1 |       2 |      1 |    60 |
|    2 |       1 |      1 |   100 |
|    2 |       1 |      2 |    90 |
|    2 |       2 |      1 |    88 |
|    3 |       1 |      1 |    70 |
+------+---------+--------+-------+

will result:

+------+--------+----------+-------+
| city | itemID | storeMin | price |
+------+--------+----------+-------+
|    2 |      1 |        1 |   88 |
|    3 |      1 |        1 |    70 |
|    2 |      2 |        1 |    90 |
|    1 |      1 |        2 |    60 |
+------+--------+----------+-------+