RE : How to do a Top-N analysis, while avoiding subqueries
- From: Marius Raicu <mariu200@xxxxxxxxx>
- To: tristan.van.essen@xxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 7 Jun 2006 12:07:27 +0200 (CEST)
RANK()/DENSE_RANK() and in general analytic functions? - see oracle
datawarehousing guide
Regards
tristan.van.essen@xxxxxxxxxxxxx a écrit :
st1\:*{behavior:url(#default#ieooui) } 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@xxxxxxxxxxxxx
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.
Regards,
Marius
__________________________________________________
Do You Yahoo!?
En finir avec le spam? Yahoo! Mail vous offre la meilleure protection possible
contre les messages non sollicités
http://mail.yahoo.fr Yahoo! Mail
Other related posts:
- » RE : How to do a Top-N analysis, while avoiding subqueries
