Posted by Erland Sommarskog on 09/21/07 22:12

Mark (mnbayazit@gmail.com) writes:
> Thanks guys. You'd think they'd have a "standard" (and simple) method
> for doing this eh?

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]