Re: How can I get the first 20 records !!

From: Tony <andrewst_at_onetel.net.uk>
Date: 20 Mar 2003 12:24:35 -0800
Message-ID: <c0e3f26e.0303201224.11da158a_at_posting.google.com>

bigjobbies_at_hotmail.com (Ethel Aardvark) wrote in message news:<1a8fec49.0303200627.215e9efb_at_posting.google.com>...
> Same thing, but more confusing. And you can certainly use it to narrow
> the result set down - it has few other practical uses!

Yes, it narrows the result set down - but done wrongly, it will not get you the first N rows in order. Example:

SQL> select ename from emp
  2* order by ename;

ENAME

---

ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD 14 rows selected.

SQL> select ename from emp
  2 where rownum < 5
  3* order by ename;

ENAME

---

ALLEN
JONES
SMITH
WARD This is because it got the first 4 rows first, THEN ordered them by ename. To ensure it orders first then narrows:

SQL> select ename from
  2 ( select ename from emp
  3 order by ename
  4 )
  5 where rownum < 5
  6 /

ENAME

---

ADAMS
ALLEN
BLAKE
CLARK Received on Thu Mar 20 2003 - 21:24:35 CET