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@accenture.com