Re: SQL*PLUS CHALLANGE

From: Izack Varsanno <izack_at_ibm.net>
Date: 1996/09/28
Message-ID: <52k99g$2nlo_at_news-s01.ny.us.ibm.net>#1/1


Roya Mehrfar <roya_mehrfar_at_qmbridge.calstate.edu> wrote:

>I have a sqlplus statement with an order by that I need to retrieve only
>the first 25 rows. I tried where rownum < 26 but it trows away my order
>by because I would like to do the order by first and then get the first
>25 rows.My statement looks like:
 

>select name, address
>from emp
>where city='LA'
>order by name;
 

>If I add rownum<26 it gets the first 25 and then orders by name, but I
>want to order by first.

Try this :
select name, address from (select * emp where city = 'LA') v1

         where 25 > 
              (select count(*) from 
                           (select * from emp where city='LA') v2 
                where v2.name < v1.name )
order by name;

>Thanks for any help

Hope you don't have any performace problems. ;-)

Bye for now,
  Izack Varsanno. Received on Sat Sep 28 1996 - 00:00:00 CEST

Original text of this message