Hi,

In sql query what is the difference between rowid and rownum ?

As I know that :

rowid is the unique index number of every row of table maintaining by database automatically.

rownum is the sequential number of rows in the resultset object.

But my confusion is if I write query like below for extracting 1st row

select * from table_name where rownum = 1 Then it is working fine

But if I write query like below for extracting 3rd row

select * from table_name where rownum = 3 Then it is returning zero row.

for that i have to write query like below

select * from table_name where rowid in(
(select rowid from table_name where rownum <= 3)
minus
(select rowid from table_name where rownum < 3)
)

Can any one please clarify me.

Thanks & Regards
Bikash

rowid is an internal representation of a row in a table, it is effectively the primary key and can be used for fast access to the row (but can chance)

rownum is a function of a result set of your query, i.e select * from emp where rownum = 1 will get the first row of your result set.

So rowid is a function of the table. rownum is a function of the result set

This is rather dependant on the particular implementation of SQL, don't you think?

There is no such thing as either a 'rowid' or a 'rownum' in the SQL language specification.

Jeff Mason
Custom Apps, Inc.
[email protected]