Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: How to do a Top-N analysis, while avoiding subqueries

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

From: LS Cheng <exriscer_at_gmail.com>
Date: Wed, 7 Jun 2006 12:08:15 +0200
Message-ID: <6e9345580606070308y43b8fddds444b163a2bdcf6ee@mail.gmail.com>


Hi

Have you tried Analytic Functions? Such as ROW_NUMBER().

Regards

LSC On 6/7/06, tristan.van.essen_at_accenture.com <tristan.van.essen_at_accenture.com> wrote:
>
> 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.
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jun 07 2006 - 05:08:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US