Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newscon02.news.prodigy.com!prodigy.net!cyclone.swbell.net!cyclone-sf.pbi.net!216.218.192.242!news.he.net!chekhov.conxion.net!news.oracle.com!not-for-mail
Message-ID: <42F765F1.6050200@gmail.com>
From: amogh <amogh.r@gmail.com>
Reply-To:  amogh.r@gmail.com
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.misc
Subject: Re: selecting top performers
References: <1123500018.839903.68240@g14g2000cwa.googlegroups.com> <42F75B8C.4030509@gmail.com> <3lp676F13oli6U1@individual.net>
In-Reply-To: <3lp676F13oli6U1@individual.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 39
Date: Mon, 08 Aug 2005 19:32:25 +0530
NNTP-Posting-Host: 152.69.155.88
X-Trace: news.oracle.com 1123510681 152.69.155.88 (Mon, 08 Aug 2005 07:18:01 PDT)
NNTP-Posting-Date: Mon, 08 Aug 2005 07:18:01 PDT
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:121106

Robert Klemme wrote:
> amogh wrote:
> 
>>baroo wrote:
>>
>>>Hi
>>>
>>>How do I select the top 20 earners from my database?
>>>
>>>urgent.
>>>
>>>cheers
>>>
>>
>>The simplest top(n) query you can write:
>>
>>SELECT a.earnings FROM
>>(SELECT earnings FROM
>>  YOUR_TAB
>>  ORDER BY earnings DESC
>>)a
>>WHERE ROWNUM < 21
>>
>>
>>Regards,
>>Amogh
> 
> 
> If I'm not mistaken you can even omit the query nesting and use the ROWNUM
> filter on the inner query directly.
> 
>     robert
> 
Every row is associated with ROWNUM which is a psuedo column. A WHERE 
clause with ROWNUM=1 will/may not fetch you the exact first row. It can 
be 'any' row in the table. So using ROWNUM directly in the inline query 
will now fetch the top 20 rows. In the above case, ROWNUM is run on a 
resultset which already has the ROWNUM column set for the top 20 
rows(because of the ORDER BY). That would give you the exact top n rows!