Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 3rd, 2004, 02:14 AM
Authorized User
Join Date: Jul 2003
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Difference between rowid and rownum in sql query??


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)
(select rowid from table_name where rownum < 3)

Can any one please clarify me.

Thanks & Regards

Old November 9th, 2006, 03:50 PM
Registered User
Join Date: Nov 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

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

Old November 9th, 2006, 03:58 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Rownum is SQL SERVER rubina SQL Server 2000 3 December 2nd, 2006 10:57 AM
Flag Unique RowID in Sql Server 2000 mkhan SQL Server 2000 4 November 6th, 2005 11:41 PM
Difference between '' and "" in sql server 2000 shoakat Classic ASP Databases 1 September 11th, 2004 04:57 AM
rownum field in sql server mateenmohd SQL Server 2000 2 June 12th, 2003 04:48 PM
sql server 2000 rownum mateenmohd Classic ASP Databases 1 June 9th, 2003 03:14 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.