June 15, 2004 at 2:35 am
I want to get the maximum of sum with one select statement.
The database is NorhtWind and this is the query.
select orderid, sum(quantity)
from [order details]
where orderid between 11000 and 11005
group by orderid
order by 2 desc
I know that i can use this statement:
select Top 1 orderid, sum(quantity)
from [order details]
where orderid between 11000 and 11005
group by orderid
order by 2 desc
or to use temporery tables but i am interested is there any different way to get only one row with maximum sum per orderid.
thanks
Alex
June 15, 2004 at 2:56 am
what about select max(sum(quantity)) from ...
cheers
dbgeezer
June 15, 2004 at 4:45 am
select max(qty) from (
select orderid, qty = sum(quantity)
from [order details]
where orderid between 11000 and 11005
group by orderid
) a
June 16, 2004 at 12:32 am
THANKS Amit
That is what i wanted, but how can i see and order id for maximum sum.
Alex
June 16, 2004 at 5:17 am
Try this:-
SELECT c.OrderId, b.[Max Qty]
FROM
(SELECT MAX(Qty) AS [Max Qty]
FROM
(SELECT OrderId, SUM(Quantity) AS Qty
FROM [Order Details]
WHERE OrderId BETWEEN 11000 AND 11005
GROUP BY OrderId) a) b
INNER JOIN
(SELECT OrderId, SUM(Quantity) AS Qty
FROM [Order Details]
WHERE OrderId BETWEEN 11000 AND 11005
GROUP BY OrderId) c
ON b.[Max Qty] = c.Qty
Might be an easier way tho., but it works.
June 17, 2004 at 2:22 am
thanks a lot
alex
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply