"Mark" <mnbayazit@gmail.com> wrote in message
news:1190088217.757875.10240@57g2000hsv.googlegrou ps.com...
> 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.
>

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