On Sep 23, 4:30 pm, --CELKO-- <jcelko...@earthlink.net> wrote:
> >> 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.

I didn't think the tables and columns were important. It's a fake
data set.
I hadn't heard of this "WITH" clause. I'll look into it some more.
Thanks again.

BTW, I ended up using something like WHERE unitsum=MAX(...) which
seems horrible and hacky, but will suffice.