Re: How to do a Top-N analysis, while avoiding subqueries

Why dont you want to use sub queries ? Any solid reason or just for curiosity ..

2006/6/7, tristan.van.essen_at_accenture.com <tristan.van.essen_at_accenture.com>:
>
> Hi folks, this is my first question in this mailing list.
>
>
>
> How do you do a top-n analysis while avoiding subqueries, but still use
> group functions on other columns?
>
>
>
> I have following Query:
>
>
>
> SELECT COUNT(city) quantity, city
>
> FROM customer
>
> GROUP BY city
>
> ORDER BY quantity DESC;
>
> This works fine so far with the exception that I only want the first
> record returned, instead of all. I tried to solve the problem with the
> ROWNUM function, but it won't work well. The problem here is that I must
> include ROWNUM in the GROUP BY clause, else the query fails. But then the
> result isn't right anymore. I wish there was any sort of LIMIT BY clause,
> like there is in MySQL.
>
>
>
> How can I solve this, while not using any subqueries?
>
>
>
> Regards,
>
> Tristan
>
>
>
>
>
>
> ------------------------------------------------------------------------------------
>
> Tristan van Essen
>
> Accenture Technology Solutions AG
>
> Baslerstrasse 60
>
> CH-8048 Zürich
>
> Office: +41 44 405 3245
>
> Mobile: +41 79 654 0592
>
> Email: tristan.van.essen_at_accenture.com
>
>
>
> This message is for the designated recipient only and may contain
> privileged, proprietary, or otherwise private information. If you have
> received it in error, please notify the sender immediately and delete the
> original. Any other use of the email by you is prohibited.
>

--
http://www.freelists.org/webpage/oracle-l