Re: How to get the first four rows???

From: Darius Carr <carrdj_at_logica.com>
Date: 1996/06/14
Message-ID: <4pr7n5$5m2_at_romeo.logica.co.uk>#1/1


Yes I think you have as this assumes that you do not care which 4 rows you get back.

e.g. If spriden table contains

          10, 1,22, 17, 3,5

      select spriden_pidm
      from   spriden
      where rownum < 5
      order by spriden_pidm

     may well return 1, 10, 17 ans 22

     and not  1,3,5,10

We came across this when developing a purchasing system where we wanted to retrieve to top X ranked suppliers. SQL applies the 'where' statements first (i.e. get the first 4 rows found) then the order by. What we wanted was the first X ordered rows. We did this by populating a temnporary table with the selected values and a ranking order column.    

It it also probably possible to do a select of the following format using count(*) etc althrouh I have not tried this and it may not be very fast.

select value
from table
where value < (select 5th largest value in subquery(s) ) order by value

Regards

Darius Carr

>re. limiting rows returned in oracle: surely just
 

>Connected to:
>Oracle7 Server Release 7.1.5.2.3 - Production Release
>PL/SQL Release 2.1.5.2.0 - Production
 

>SQL> select spriden_pidm from saturn.spriden
> 2 where rownum < 5
> 3 /
 

>SPRIDEN_PIDM
>------------
> 184
> 185
> 186
> 187
 

>will do the trick. or have i missed something... :)



All opinions are my own and do not necessarlily refect the opinion of my employers.
Received on Fri Jun 14 1996 - 00:00:00 CEST

Original text of this message