--select first row only
select * from
(select empno, ename, job, mgr, hiredate, sal, comm, deptno,
rank() over (order by rownum asc) ranking
from emp)
where ranking = 1
/

--select second row only
select * from
(select empno, ename, job, mgr, hiredate, sal, comm, deptno,
rank() over (order by rownum asc) ranking
from emp)
where ranking = 2
/

--select rows 2 thru 4 only
select * from
(select empno, ename, job, mgr, hiredate, sal, comm, deptno,
rank() over (order by rownum asc) ranking
from emp)
where ranking between 2 and 4
/

--select last row only
select * from
(select empno, ename, job, mgr, hiredate, sal, comm, deptno,
rank() over (order by rownum desc) ranking
from emp)
where ranking <= 1
/

--select last three rows only
select * from
(select empno, ename, job, mgr, hiredate, sal, comm, deptno,
rank() over (order by rownum desc) ranking
from emp)
where ranking < 4
/

-----Original Message-----
From: Anthony Molinaro
[mailto:oracledba-ezmlmshield-x99270386.[Email address protected]
Sent: Thursday, April 07, 2005 9:16 AM
To: LazyDBA Discussion
Subject: RE: About ROWNUM


Rekha,
Rownum is assigned to a row after it is fetched.

There is no such thing as the 4th row as the table
is not stored in any order. So, when you say "rownum <= 4"
you can get any 4 random rows.

Now, you may notice that "rownum = 1" does indeed work,
and this is because oracle has to perform at least
one check to see if the table does in fact have a row,
so "rownum = 1" will always work (as there would be no "first" or
"last"
with 1 row, it is both first and last, it is the only row).

> It is possible to create view based on non-exist table .

??? not sure what you mean there, but check the ora doc for the FORCE
option of the CREATE VIEW statement


- a

-----Original Message-----
From: Rekha Kadam
[mailto:oracledba-ezmlmshield-x99255852.[Email address protected]
Sent: Thursday, April 07, 2005 9:07 AM
To: LazyDBA Discussion
Subject: About ROWNUM

Hi

why rownum always have to use with less than operator. Like

[Email Address Removed] select EMPLOYEE_ID,rownum from employee where rownum < 4;

EMPLOYEE_ID
-----------
7499
7505
7506

But following query does not return data.
1)
[Email Address Removed] select EMPLOYEE_ID,rownum from employee where rownum > 4;

no rows selected.
2)
[Email Address Removed] select EMPLOYEE_ID,rownum from employee where rownum = 4;

no rows selected.

Plz any one can me valid reason for it.

and one more

It is possible to create view based on non-exist table .


Thanks in advance

With Regards,
Rekha.



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html

Oracle LazyDBA home page