|
|
Posted by --CELKO-- on 09/23/07 23:30
>> You'd think they'd have a "standard" (and simple) method for doing this eh? <<
It is a little hard to guess what your tables and columns look like
from your narrative. There is even a magical, universal "id" attached
to nothing in particular! I guess you meant "product_id" and do not
have an industry standard identifier to use, like UPC. I guess type
is the product type (category?) code, and that it is in the Products
table, not the Orders. But it could be the order type, customer blood
type or anything.
One Standard SQL answer would be:
WITH ProductCategorySales(product_type, sold_tot)
AS (SELECT P.product_type, SUM(O.sold_units)
FROM Orders AS O, Products AS P
WHERE O.product_id = P.product_id
GROUP BY product_type)
SELECT product_type
FROM ProductCategorySales
HAVING sold_tot = (SELECT MAX(sold_tot) FROM ProductCategorySales);
or get fancy and use some stuff not in SQL Server yet:
(SELECT P.product_type,
SUM(O.sold_units)
OVER (PARTITION BY P.product_type)
FROM Orders AS O, Products AS P
WHERE O.product_id = P.product_id)
The reason that Standard SQL does not have LIMIT or something like it,
is that SQL is a set-oriented database language, not a sequential file
language. Such things would have to be part of a cursor's ORDER BY
clause to fit into the language model.
Navigation:
[Reply to this message]
|