I have three tables:
Shop_Table
shop_id
shop_name
Sells_Table
shop_id
item_id
price
Item_Table
item_id
item_name
The Sells_Table links the item and shop tables via FK's to their ids. I am trying to get the most expensive item from each store, i.e., output of the form:
(shop_name, item_name, price)
(shop_name, item_name, price)
(shop_name, item_name, price)
(shop_name, item_name, price)
...
where price is the max price item for each shop. I can seem to achieve (shop_name, max(price)) but when I try to include the item_name I am getting multiple entries for the shop_name. My current method is
create view shop_sells_item as
select s.shop_name as shop, i.item_name as item, price
from Shop_Table s
join Sells_Table on (s.shop_id = Sells_Table.shop_id)
join Item_Table i on (i.item_id = Sells_Table.item_id);
select shop, item, max(price)
from shop_sells_item
group by shop;
However, I get an error saying that item must appear in the GROUP BY clause or be used in an aggregate function, but if I include it then I don't get the max price for each shop, instead I get the max price for each shop,item pair which is of no use.
Also, is using a view the best way? could it be done via a single query?
Please note that the query below doesn't deal with a situation where multiple items in one store have the same maximum price (they are all the most expensive ones):
SELECT
s.shop_name,
i.item_name,
si.price
FROM
Sells_Table si
JOIN
Shop_Table s
ON
si.shop_id = s.shop_id
JOIN
Item_Table i
ON
si.item_id = i.item_id
WHERE
(shop_id, price) IN (
SELECT
shop_id,
MAX(price) AS price_max
FROM
Sells_Table
GROUP BY
shop_id
);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments