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.>