Re: FETCH a Specific Amount of Rows from Query

From: sdg <nospan_at_noway.org>
Date: Wed, 23 Apr 2003 14:17:56 GMT
Message-ID: <3EA69FB8.1FD1EF61@noway.org>

And most everyone else if feed up with you.

Paul Brewer wrote:

> "Anton Buijs" <remove_aammbuijs_at_xs4all.nl> wrote in message
> news:3ea58211$0$49103$e4fe514c_at_news.xs4all.nl...
> > Where I want to add that
> > select * from table a where rownum < 10 order by a_column
> > wiil NOT return the first 10 rows in te SORTED order.
> > Oracle just stops retrieving rows after the first 10 rows are returned by
> > the database engine - and that order is unpredictable. Then those rows get
> > sorted.
> >
> > Olaf Raether <olaf_at_raether.de> schreef in berichtnieuws
> > hnvaavk2mq9p6gr11riqr8bnk6qg56q5f9_at_4ax.com...
> > |
> > | nearly the same
> > |
> > | select * from TABLE where ROWNUM < 10
> > |
> > | Olaf Raether
> > |
> > | On 22 Apr 2003 09:42:08 -0700, gilgantic_at_yahoo.com (gilgantic) wrote:
> > |
> > | >Hi,
> > | >
> > | >I know in DB2 you can use the FETCH keyword to get a specific amount of
> > rows
> > | >from a query. Example, SELECT * FROM A FETCH FIRST 10 ROWS ONLY, will
> > return
> > | >on the first 10 rows of a result set. How is this done in Oracle 8i.,
> or
> > is
> > | >it even possible? The FETCH keyword is used differently in Oracle 8i as
> I
> > | >understand it.
> > | >
> > | >Thanks!
> > | >Gil
> > |
> >

>

> a) Can anybody (apart from Rene Nyffenegger, who posted an intelligent
> answer) count up to double figures?

>

> b) Who said a SORTED order? OP said he wanted 10 rows.

>

> 1) If you want 10 rows you need rowcount < 11. If you specify rowcount < 10
> you will get 9.

>

> 2) If OP wants them in some sort of order he can use select from(select
> order by) where row count < 11.

>

> SQL> select ename from emp where rownum < 10;

>

> ENAME
> ----------
> SMITH
> ALLEN
> WARD
> JONES
> MARTIN
> BLAKE
> CLARK
> SCOTT
> KING

>

> 9 rows selected.

>

> SQL> select ename from emp where rownum < 11;

>

> ENAME
> ----------
> SMITH
> ALLEN
> WARD
> JONES
> MARTIN
> BLAKE
> CLARK
> SCOTT
> KING
> TURNER

>

> 10 rows selected.

>

> SQL> select * from (select ename from emp order by ename) where rownum < 11;

>

> ENAME
> ----------
> ADAMS
> ALLEN
> BLAKE
> CLARK
> Daddy
> FORD
> JAMES
> JONES
> Jennifer
> KING

>

> 10 rows selected.

>

> 3) I'm now fed up with this thread.

>

> Regards,
> Paul
Received on Wed Apr 23 2003 - 09:17:56 CDT