Re: FETCH a Specific Amount of Rows from Query

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Tue, 22 Apr 2003 23:20:03 +0100
Message-ID: <3ea5c03c_3@mk-nntp-1.news.uk.worldonline.com>

"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
> |
>

1. Can anybody (apart from Rene Nyffenegger, who posted an intelligent answer) count up to double figures?
2. Who said a SORTED order? OP said he wanted 10 rows.
3. If you want 10 rows you need rowcount < 11. If you specify rowcount < 10 you will get 9.
4. 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 Tue Apr 22 2003 - 17:20:03 CDT