pranab k. panda

• Aug 19th, 2006

 

Rownum does not assign the number like 1,2,3,4 to the rows as it appears.

Case-1

if the order is arranged in ascending order then the first item gets row num=1

Case-2

if the order is arranged in descending order then also the first item gets row num=1

instead to use rownum=2  use alias name for rownum

exp :-select rownum ro,columnname from table where ro=2;

If not understood pls contact me in my mail id

pranabp @ aztecsoft.com

Pranab

bhargav

• Nov 26th, 2006

 

 

 

                       We can`t use  Relational operators like ' =, >=' with rownum while comparing, since Rownum is a temporary column which is created when user writes Select statement, the rownum is create for each row after the execution of select statement, so if use > or = in where condition or select statement, as the rownum will creates after the execution of select statement there is no chance where rownum is greater than perticular value..

                    But any way statement did not gives any error if use = or >= with rownum. It diplays " NO Records Found ".

krishnaindia2007

 Profile Answers by krishnaindia2007 

• Jun 22nd, 2008

 

We should not use >, >=,  =  with rownum.
It do not show any error message but returns 0 rows.

Here
select ename,job,sal
from emp
where rownum = 2;


Rownum always assigns rownum 1 to first row and checks the condition. The condition is false. Now it takes second row and assigns rownum 1. For second row also codition is false.
For all rows the condition is false. I will not return any row.

We can solve this problem using inline views as follows

Select ename,job, sal
from  
(
 select rownum rn, ename,job,sal from emp
)
where rn =2;

vinoth.jeevanandam

 Profile Answers by vinoth.jeevanandam 

• Mar 3rd, 2009

 

You cannot use =, >, >= on rownum operator. Instead you can have,

select employee_id from
(select row_number() over(order by employee_id asc) as rn, employee_id from employees) where rn=2;

select employee_id from
(select rownum as rn, employee_id from employees) where rn=2;