Re: Limiting # of hits via SQL
Date: 1995/06/19
Message-ID: <3s4sh4$2iu_at_newsgate.dircon.co.uk>#1/1
DRathbun (drathbun_at_aol.com) wrote:
: >Subject: Limiting # of hits via SQL
: >
: >Can someone mention to me how to tell SQLplus to only return the first
: >'n' hits?
: >
: >We want to collect some stats automatically, and so we want a
: >descending sort of access count on some DB items we serve, and we just
: >want the top ten.
We use a trick that requires an index on a dummy column that contains a negative value of the value in question.
Select bla,bla,bla
from thing
where X > -1000000
and rownum < 10;
X is a negative version the value you want the top 10 of and no values of X are less than -1000000. The where clause causes the optimiser to use the index to retrieve the values in order.
It is not suitable in all situations but it works and is quick. Received on Mon Jun 19 1995 - 00:00:00 CEST