Re: SQL*PLUS CHALLANGE

Roya Mehrfar 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.
>
> I thought of maybe adding 'minus' clause, but that didn't work either.
>
> Thanks for any help

You could create an index on the name col and then force the query to use this index as follows

select/*+ INDEX(emp,name_idx)
name, address
from emp
where city='LA'
and rownum < 26;

If you do not want to create this index then you may write a PL/SQL procedure that will use a cursor to retrieve your rows and then use the DBMS_OUTPUT.PUT_LINE packaged procedure to return as many rows as you desire. I think you may lose all of the formatting features of SQLPLUS if this method is used.
> --

Regards,

Varad Acharya Received on Fri Sep 27 1996 - 00:00:00 CEST