Newsgroups: comp.databases.oracle
From: y-tamura@nriws11.nri.co.jp (Youichi Tamura)
Subject: Re: TOP TEN LISTS? HELP.
Message-ID: <D0wMy1.J56@nrigw11.nri.co.jp>
Sender: news@nrigw11.nri.co.jp
Nntp-Posting-Host: nriws11
Organization: Nomura Research Institute, Ltd.
References: <199412141723.LAA26999@motcig.cig.mot.com>
Date: Fri, 16 Dec 1994 13:03:37 GMT
Lines: 51


In article <199412141723.LAA26999@motcig.cig.mot.com> natrjns@cig.mot.com (Saikumar T Natarajan) writes:
>mwmaher@ACM.ORG wrote:
>> How do you create a listing of the top N items in a table
>> with straight SQL?  This seems like such a basic task -
>> until you try it.
>
>
>> select tab1.col
>> from   table tab1
>> where  exists
>>      (select 1
>>       from   table tab2
>>       where  tab2.col >= tab1.col
>>       having count(*) <= N)
>>order by tab1.col;
 
>>(where N is the number of items you want to display)
 
>>Disclaimer: The length of time to run this statement will be exponential to
>>            the number of rows in the table :-(
 
>>Does anyone have any better ideas?
 
>>--
 
>>John Blackburn                                          Phone: +61 7 2534634
>>jb2@qdot.qld.gov.au                                     Fax:   +61 7 8541194

This works.



>Try this
>
>SELECT ROWNUM,<column_name1>,<column_name2>,...
>FROM <table>
>WHERE ROWNUM < <N>
>
>N = number of rows you wish to retrieve
>
>
>-- 
>Saikumar T Natarajan			Voice Mail - (708) 632-3231
>Consultant


This does not work.  This only gives you the first N rows that you 
pull from the database, not the top N items.
-- 
							Yoichi Tamura
							y-tamura@nri.co.jp