Re: TOP TEN LISTS? HELP.
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