Hi,

I am trying to get the max value in a range in a way that requires a double 
aggregation.
For example, using Northwind,
from the resultset of this query:

select ProductName, OrderID, SUM(Quantity) from OrderDetails
group by ProductName, OrderID
order by ProductName, sum(quantity) Desc


I want to get the  OrderID and quantity that ordered the most for a 
ProductName
such that I get
Alice Mutton          10607    100
Aniseed Syrup	10540	60
Boston Crab Meat	10895	91
....

Thanks in advance
Habib 

Here is one method:

SELECT ProductID, OrderID, qty
FROM (
SELECT ProductID, OrderID, SUM(Quantity) AS qty,
        ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SUM(Quantity) DESC) AS rk
FROM [Order Details]
GROUP BY ProductId, OrderID) AS P
WHERE rk = 1
ORDER BY qty DESC;

-- 
Plamen Ratchev
http://www.SQLStudio.com

Thank you Plamen!


"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message 
news:loidnevJHPth5ZjWnZ2dnUVZ_vli4p2d@speakeasy.net...
> Here is one method:
>
> SELECT ProductID, OrderID, qty
> FROM (
> SELECT ProductID, OrderID, SUM(Quantity) AS qty,
>        ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY SUM(Quantity) 
> DESC) AS rk
> FROM [Order Details]
> GROUP BY ProductId, OrderID) AS P
> WHERE rk = 1
> ORDER BY qty DESC;
>
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com 

>> I want to get the order_id and quantity that ordered the most for a product_name  <<

WITH P(product_name, order_id, qty_tot)
AS
(SELECT product_name, order_id, SUM(order_qty)
  FROM Order_Details
 GROUP BY product_name, order_id)

SELECT P1.*
  FROM P AS P1
 WHERE P1.qty_tot
     = (SELECT MAX (P2.qty_tot)
          FROM P AS P2
         WHERE P1.product_name = P2.product_name);

This will handle ties. Get the totals in a CTE, then use it to find
the maximum total quantity.