So, I have a query

SELECT type
FROM order, product
WHERE order.id = product.id
GROUP BY type
ORDER BY sum(units) DESC

but I only want the first row. MS SQL 2005 doesn't seem to support
"LIMIT" or "FIRST" which is unfortunate. I can shove that whole query
into another one that checks the MAX, but then I can only get the MAX
number of units within a group, when I want to know the type that has
the max units in any group.

select top 1 type from (
SELECT type,sum(units) s
FROM order, product
WHERE order.id = product.id
GROUP BY type ) t1 order by s desc


On Sep 18, 12:03 pm, Mark <mnbaya... (AT) gmail (DOT) com> wrote:

"Mark" <mnbayazit (AT) gmail (DOT) com> wrote

LIMIT and FIRST are not standard SQL features. Nor is TOP but it achieves
something similar in SQL Server:

SELECT TOP 1 WITH TIES type
FROM ord, product
WHERE ord.id = product.id
GROUP BY type
ORDER BY SUM(units) DESC;

Alternatively you can do the following using standard ANSI SQL, which should
work on many different platforms:

SELECT type
FROM ord, product
WHERE ord.id = product.id
GROUP BY type
HAVING SUM(units) >= ALL
(SELECT DISTINCT SUM(units)
FROM ord, product
WHERE ord.id = product.id
GROUP BY type);

(untested)

--
David Portas

David Portas wrote:

This could return multiple values if there's a tie for most
common type.

On 18 Sep, 08:30, Ed Murphy <emurph... (AT) socal (DOT) rr.com> wrote:
Correct. So could the version using TOP WITH TIES. Mark said: "I want
to know the type that has the max units in any group". If there is
more than one such type then the specification is incomplete because
Mark doesn't say which one should come "first". Rather than pick a
random row or make the assumption that there is only one row I decided
it was safer to return everything - that way Mark can decide for
himself whether he needs to refine his spec.

--
David Portas

On Sep 18, 5:27 am, David Portas
<REMOVE_BEFORE_REPLYING_dpor... (AT) acm (DOT) org> wrote:
Thanks guys. You'd think they'd have a "standard" (and simple) method
for doing this eh?

Mark (mnbayazit (AT) gmail (DOT) com) writes:
They have:

WITH unitsums (type, unitsum) AS (
SELECT type, SUM(units)
FROM order, product
WHERE order.id = product.id
GROUP BY type
), ranks (type, rank) AS (
SELECT type, rank AS (ORDER BY unitsum)
FROM unitsums
)
SELECT type FROM ranks WHERE rank = 1

This is how you would write this query in ANSI SQL. This particular
syntax is accepted in SQL 2005, and I believe it should run on Oracle
as well. I will have to confess that I don't really expect MySQL to
support this.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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.

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