Re: first n rows...

"Igor Apostoloski" <apostol_at_vip.hr> wrote in message news:<9td9sn$rit$1_at_sunce.iskon.hr>...
> Hi,
> I need to return first n rows from a query using oracle.
> I know that one (hopefully not the only ;) way is to add rownum<n in my
> where clause, e.g.
> select * from items where rownum<1000.
>
> I have a problem because I want to fetch the first 1000 rows from a table
> ORDERED by NAME (for example).
> The only way I know how to do this is :
> SELECT * FROM (SELECT * FROM ITEMS ORDER BY NAME) WHERE ROWNUM<=1000;

select a.* from items a
where exists (select name from items where rownum< 500 where name = a.name)
/

Note: select name from items -> will use a Fast Full Index Scan which is also in order.
>
> This is very, VERY slow, because the inner select is executed on all records
> and the outer select returns the 1000 rows.
> if I do
> SELECT * FROM ITEMS WHERE ROWNUM<=1000 ORDER BY NAME;
> I'll get the first 1000 rows from a table ordered by name, which is of no
> use to me!
>
> I've tested similar query on 500 records on different databases on the same
> server, and the results are:
> ORACLE 8.1 :
> SELECT * FROM (SELECT * FROM ITEMS ORDER BY NAME) WHERE ROWNUM<=500;
> 2.19 sec
>
> MS SQL SERVER 2000 :
> SELECT TOP 500 FROM ITEMS ORDER BY NAME
> 0.7 sec
>
> MYSQL 3.23.28 GAMMA :
> SELECT FROM ITEMS ORDER BY NAME LIMIT 500;
> 0.29 sec
>
> All the databases have an index on NAME and contain the same data and
> datatypes.
>
> Is there a way to do something like LIMIT or TOP statement in ORACLE?
>
> thanx, igor
Received on Fri Nov 23 2001 - 14:06:43 CET