Please note, that there is at least 3 types of Top-N analysis, as Tom
Kyte in "Expert One-On-One Oracle" suggests (p.566). You have to think
what you will want to do, if few persons/products/etc will have the
same rank... show only first (N rows), show all (than query may return
more than N rows), or don't show this items at all, if total row
number will be more than N (than query may return less than N rows).

So, remember, that Top-N query is a tricky one!


On 6/7/06, tristan.van.essen@(protected)
<tristan.van.essen@(protected):
>
>
>
> 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@(protected)
>
>
>
>
>
>
> 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.


--
Best regards,
Edgar Chupit
callto://edgar.chupit
--
http://www.freelists.org/webpage/oracle-l