Why this query returns null?  Why rownum does not work with equality?

select * from emp where rownum = 10;
 

SQL> select rowid,rownum from emp ;
ROWID                  ROWNUM
------------------ ----------
AAAHcIAABAAAMuiAAA          1
AAAHcIAABAAAMuiAAB          2
AAAHcIAABAAAMuiAAC          3
AAAHcIAABAAAMuiAAD          4
AAAHcIAABAAAMuiAAE          5
AAAHcIAABAAAMuiAAF          6
AAAHcIAABAAAMuiAAG          7
AAAHcIAABAAAMuiAAH          8
AAAHcIAABAAAMuiAAI          9
AAAHcIAABAAAMuiAAJ         10
AAAHcIAABAAAMuiAAK       ! 11

ROWID                  ROWNUM
------------------ ----------
AAAHcIAABAAAMuiAAL         12
AAAHcIAABAAAMuiAAM         13
AAAHcIAABAAAMuiAAN         14
AAAHcIAABAAAMuiAAO         15
AAAHcIAABAAAMuiAAP         16
16 rows selected.

SQL> select * from emp where rowid='AAAHcIAABAAAMuiAAJ' ;
     EMPNO ENAME           JOB              MGR HIREDATE         SAL       COMM
---------- --------------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7844 TURNER          SALESMAN        7698 08-SEP-81       1500          0
        30

What I inquire is only this rowid is changed to rownumber for the userfriendly environment..and in order to retrieve it we should use only this rowid..This is what i feel and i may be right or wrong.just want to share what i feel.

Padma Raj
 

rownum is not stored in database, it is a pseudocolumn, and generated only when we try to retrieve data from the tables. oracle assigns the sequential number in the same order it retrieves the records from the table.

Now if you use "<" then oracle can find out the records rownum and returns the records. while in case of equality, its not possible to identify a record order in which it is going to be returned.

Sumit
 

Its wrong query

select * from emp where rownum = 10;

Right query is : select * from emp where (cloumn name) = 10;

Upendra Trivedi
 

To remove duplicate rows using rownum,  do the following:

select rownum, <column name> from <table name>;

you will get the list of records with unique rownum. Even the duplicate records will have different rownum.

Now, you can use the DELETE statement to remove one of the duplicate record.

delete <table name> where  rownum= X;  ( where X is the rownum of the duplicate record)

Hope you have understood!!!

Ruby

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips