Re: TOP TEN LISTS? HELP.

From: L. Scott Johnson <sjohnson_at_math.scarolina.edu>
Date: 14 Dec 1994 18:56:25 GMT
Message-ID: <3cnf4p$qlk_at_redwood.cs.scarolina.edu>


natrjns_at_cig.mot.com (Saikumar T Natarajan) writes:

>mwmaher_at_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.
 

>> Thank you in advance.
 

>> how about
 

>> 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 :-(

>Try this
 

>SELECT ROWNUM,<column_name1>,<column_name2>,...
>FROM <table>
>WHERE ROWNUM < <N>
 

>N = number of rows you wish to retrieve

Unfortunately, this only returns N rows, not the *top* N rows. A top N list requires an ordering, which invalidates the rownum column.

Another solution (akin to a proper, solution by mwmaher, above):

select tab1.col
from table tab1
where N >= (select count(*)

	  from table tab2
	  where tab2.col < tab1.col

)
order by tab1.col;

(This runs faster for me than mwmaher's example, but I couldn't tell you why).

L. Scott Received on Wed Dec 14 1994 - 19:56:25 CET

Original text of this message