Re: Limiting # of hits via SQL

P.M.Shaw_at_sheffield.ac.uk (Pete Shaw) wrote:

>In article <JOHNS.95Jun13115024_at_adwyer.gsfc.nasa.gov>,
> johns_at_adwyer.gsfc.nasa.gov (Steve Johns) says:
 

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

>Try using the pseudo-column rownum.
 

>Add the following to your 'where clause'...
 

><< Your query here >>
>and rownum < 11
 

>In most cases this should work.
 

>Cheers Pete.

This will only work if you are not sorting the result. If you use ORDER BY then rownum is useless. If you want to get a top 10, try (as scott/tiger):

select ename, sal
  from emp
 where -sal in

       (select -sal
          from emp)

The subquery does the sorting for you.

Mike Madland
mikemad_at_asymetrix.com Received on Wed Jun 28 1995 - 00:00:00 CEST