Sheesh Ken I didn't want you to goto too much trouble. I was just browsing a website and copied the query and question straight from it, since everyone said they were bored!
I had quick stab at it but came across the problem you listed regarding two items with the same quantity, and left it at that. Bit out of my league! Thanks, anyway. I'll submit the answer and pass on anything you get for it!! Cheers, Andrew -----Original Message----- From: Ken Schaefer [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 31 July 2002 10:56 AM To: ActiveServerPages Subject: Re: slow day ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ From: "Andrew Haslett" <[EMAIL PROTECTED]> Subject: RE: slow day : SELECT o.OrderID, : Max(od.Quantity) as TopItem, : Sum(od.Quantity) as TotalBought : FROM : Orders o, : [Order Details] od, : Products p : WHERE : o.OrderID = od.OrderID AND : od.ProductID=p.ProductID : GROUP BY o.OrderID : ORDER BY TotalBought DESC : : Display the name of the product that matches the TopItem column... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~: You can't do it the way you are currently running the query. To get the ProductName for the TopItem you need to ProductID for that item, but you are not able to select that anywhere in the outer query as long as you are doing the SUM() - (well, you can't as far as I can work out - maybe David P or Tore could work it out) You would need to use a subquery, but one problem is that for some orders, the subquery will return more than one value. For example, if you look at OrderID 10252, there are two items that have a quantity of 40 OrderID ProductID UnitPrice Quantity Discount ----------- ----------- --------------------- -------- --------------------- --- 10252 20 64.8000 40 5.0000001E-2 10252 33 2.0000 25 5.0000001E-2 10252 60 27.2000 40 0.0 Your existing query can return Max(Quantity) because that is 40, but which ProductName do you want to return? The following works, as far as I can tell. It's probably the worst way to write the query - David or Tore could probably give you a much better way of doing it. I took out the reference to the Orders table, since that was unnecessary - the join you were doing before would not have returned any Orders that did not have a corresponding entry in the Order Details table, and no Order Detail can exist without a corresponding entry in the Orders table. Since you only wanted the OrderID, you can get that from the Order Details table by itself. USE Northwind SELECT a.OrderID, Max(a.Quantity) AS TopItem, ( SELECT TOP 1 c.ProductName FROM [Order Details] b INNER JOIN Products c ON b.ProductID = c.ProductID WHERE b.OrderID = a.OrderID AND NOT EXISTS ( SELECT NULL FROM [Order Details] d WHERE d.OrderID = b.OrderID HAVING Max(d.Quantity) < b.Quantity ) ), Sum(a.Quantity) AS TotalBought FROM [Order Details] a GROUP BY a.OrderID ORDER BY TotalBought DESC Cheers Ken --- You are currently subscribed to activeserverpages as: [EMAIL PROTECTED] To unsubscribe send a blank email to %%email.unsub%% --- You are currently subscribed to activeserverpages as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED]
