title: Trying to Find Most Popular Product
body: <p>I am trying to write a query that pulls the top 5 customers by the amount of all products they have purchased. No problem. But in addition, I need to find the product that was the most popular for each of these 5 customers. </p>
  
  <p>I think I can do this by looking at what product they bought the most (so what productID's quantity is the max quantity), but I'm not quite sure how to get at the productID. Any ideas? Am I going about this all wrong? Thanks!</p>
  
  <pre><code>select top 5 c.CustomerID, sum(sod.orderqty) AS 'Amount Purchased', 
  max(sod.orderqty) AS 'Most Purchased'   
  from Sales.Customer c
  inner join Sales.SalesOrderHeader soh on soh.CustomerID = c.CustomerID
  inner join Sales.SalesOrderDetail sod on sod.SalesOrderID = soh.SalesOrderID
  inner join Production.Product p on p.ProductID = sod.ProductID
  group by c.CustomerID
  order by 'Amount Purchased' desc
  </code></pre>
  
  <p>I tried the query below, but for some reason, including the p.name in the group by completely throws off the max. For example, in this query, the 'Most Purchased' should be 20, but it shows as 18 (which is the 2nd-next max.)</p>
  
  <pre><code>select top 5 soh.CustomerID, sum(sod.orderqty) AS 'Amount Purchased'
  , max(sod.orderqty) AS 'Most Purchased', p.name 
  from Sales.SalesOrderHeader soh 
  inner join Sales.SalesOrderDetail sod on sod.SalesOrderID = soh.SalesOrderID
  inner join Production.Product p on p.ProductID = sod.ProductID
  where soh.CustomerID = 29705
  group by soh.CustomerID, p.name
  order by 'Amount Purchased' desc
  </code></pre>