Re: How to get the first four rows???

From: Mark Edwards <100325.223_at_compuserve.com>
Date: 1996/05/18
Message-ID: <319DA28A.3B1F_at_compuserve.com>#1/1

Ayokunle Giwa wrote:
>
> Hi I think this will work......
>
> select x_date
> from (select x_date
> from y_date)
> where rownum < 5
> order by x_date desc;

Hi Ayokunle.

I think that this has raised an interesting problem, one for which I have not yet been able to devise a workaround.

Take for example the query:

SQLDBA> select ename from emp ;
ENAME

---

Pedersen
aldjflkfj
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
...

and the query

SQLDBA> select ename from emp order by ename; ENAME

---

ADAMS
BLAKE
CLARK
FORD
HyperData
JAMES
JONES
KING
MARTIN
MILLER
....

As soon as the order by clause is added, the percieved result that can be gained by using rownum is changed:

SQLDBA> select ename from emp where rownum<6; ENAME

---

Pedersen
aldjflkfj
JONES
MARTIN
BLAKE
5 rows selected.

and the 'incorrect'...

SQLDBA> select ename from emp where rownum < 6 order by ename; ENAME

---

BLAKE
JONES
MARTIN
Pedersen
aldjflkfj
5 rows selected.

where the intention might probably be:

SQLDBA> select ename from emp order by ename where rownum < 6 (sic) ENAME

---

ADAMS
BLAKE
CLARK
FORD
HyperData
5 rows selected.

It might be possible to work this around if the create view statement allowed an order by clause, but it doesn't.

Comments etc..?

Many thanks

Mark Edwards Received on Sat May 18 1996 - 00:00:00 CEST