Max returning another field

[AlexHeddell]

I am trying to write a query that returns the customer who has bought the most of a product.

I have an Order table with CustomerID, ProductID and QtySold (amongst other things, obviously!!). If I do:

select CustomerID,ProductID,sum(QtySold) SumQtySold
into #Temp
from orders
group by CustomerID,ProductID

I can then find the max of the Product but I can't link it back to the customer

I'm sure I am missing something obvious here. Any help much appreciated

Alex

 

[skuhlman]

select NameTable.* from NameTable 
    join (select top 1 NameID, count(*) as RecCnt from OrderTable where ItemNumber = 999 group by NameID order by RecCnt desc) b 
        on NameTable.NameID = b.NameID

 

[kavin]

Hi..
I m not clear what actually you want, so trying to get some idea.. If you want to show all the customers who having max quantity sold for any product that u can use the following query:

select * from
(
select CustomerID,ProductID,sum(QtySold) as 'Qty' from orders
group by CustomerID,ProductID
) as MaxQty_customer
where ProductID+Qty in (select ProductID + max(Qty) from (
select CustomerID,ProductID,sum(QtySold) as 'Qty' from orders
group by ProductID
) as MaxQty_customer2 )


NOTE: If product Id is varchar then convert Qty into varchar first.

 

[AlexHeddell]

Got this now. Thanks for your help. For reference, here is the code I am using:

select s.ProductID,(select top 1 CustomerID
from Order
where ProductID = s.ProductID
group by CustomerID
order by sum(Quantity) desc) CustomerID
from Stock s

 

[ESquared]

How about a star for the folks who helped you, if they did? Click on "Thank X for this valuable post."

For what it's worth, the correlated subquery method you're using could be expensive if the Stock table is very large. Better might be a derived table with a group by clause.