|
|
Posted by David Portas on 09/18/07 05:41
"Mark" <mnbayazit@gmail.com> wrote in message
news:1190088217.757875.10240@57g2000hsv.googlegroups.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
Navigation:
[Reply to this message]
|