Ken, thought I'd post this answer as it shows all products if more than one
have the same maximum quantity
SELECT
Grouped.OrderID,
Grouped.TopItem,
Grouped.TotalBought,
dbo.Products.ProductName
FROM
(
SELECT
dbo.Orders.OrderID,
MAX(OrderDetails.Quantity) AS TopItem,
SUM(OrderDetails.Quantity) AS TotalBought
FROM
dbo.Orders
INNER JOIN
dbo.[Order Details] OrderDetails
ON
dbo.Orders.OrderID = OrderDetails.OrderID
INNER JOIN
dbo.Products
ON
OrderDetails.ProductID = dbo.Products.ProductID
GROUP BY
dbo.Orders.OrderID
)
Grouped
INNER JOIN
dbo.[Order Details]
ON
Grouped.OrderID = dbo.[Order Details].OrderID
AND
Grouped.TopItem = dbo.[Order Details].Quantity
INNER JOIN
dbo.Products
ON
dbo.[Order Details].ProductID = dbo.Products.ProductID
ORDER BY
Grouped.orderid DESC
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]
