Re: TOP TEN LISTS? HELP.
From: John Blackburn <jb2_at_avalon>
Date: 14 Dec 1994 03:29:00 GMT
Message-ID: <3clops$3oc_at_camelot.qdot.qld.gov.au>
Date: 14 Dec 1994 03:29:00 GMT
Message-ID: <3clops$3oc_at_camelot.qdot.qld.gov.au>
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 :-(
Does anyone have any better ideas?
-- John Blackburn Phone: +61 7 2534634 jb2_at_qdot.qld.gov.au Fax: +61 7 8541194Received on Wed Dec 14 1994 - 04:29:00 CET