Groupby problem

[partridge80]

Consider the following:

Shop Product code Price
1 001 200
1 002 300
1 003 400
2 008 370
2 011 230
2 020 840
3 066 500
3 068 610

How can I groupby to show only the highest priced product code for each shop?

i.e. for shop 1 - 003, shop 2 - 020, shop 3 - 068

Probably very easy - but am still fairly new to all this - Many thanks!

 

[MaffewW]

try this

select shop,max(price) as maxprice
from yourlist
group by shop

 

[JamesLean]

MaffewW,
Your query will only return the highest price for each shop - not the product which has that price.

partidge80,
This should work for you:

SELECT shop, productcode
FROM shoptable s1
WHERE price = (
  SELECT MAX(price)
  FROM shoptable
  WHERE shop = s1.shop
)

--James

 

[partridge80]

Thanks James, unfortunately I had over simplified the problem. What if I also wanted the total 'Price' per shop. i.e. Shop1 - 900, shop2 - 1440, etc.

 

[JamesLean]

This should do that:

SELECT Shop, productcode AS MostExpensive, (SELECT SUM(price) FROM #shop WHERE shop = s1.shop) AS TotalPrice
FROM #shop s1
WHERE price = (
  SELECT MAX(price)
  FROM #shop
  WHERE shop = s1.shop
)

--James

 

[partridge80]

James,

What is the significance of s1 - I don't know what it means?

 

[JamesLean]

That is just the alias I gave the #shop table in the main query, to allow reference to it in the subqueries. It is assigned in line 2 (the FROM clause).

(BTW, I've just realised I left the # on the table name from when I was trying it out here - obviously you would replace this with your actual table name!) --James