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

From: Marius Raicu <mariu200_at_yahoo.com>
Date: Wed, 7 Jun 2006 12:07:27 +0200 (CEST)
Message-ID: <20060607100727.6772.qmail@web60911.mail.yahoo.com>

RANK()/DENSE_RANK() and in general analytic functions? - see oracle datawarehousing guide

Regards

tristan.van.essen_at_accenture.com 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_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.

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

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

Received on Wed Jun 07 2006 - 05:07:27 CDT