Re: TOP TEN LISTS? HELP.

In article <199412141723.LAA26999_at_motcig.cig.mot.com> 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.
>
>
>> 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_at_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_at_nri.co.jp