Q
Problem solve Get help with specific problems with your technologies, process and projects.

Help using ROWNUM in queries

I ran the following within Oracle SqlPlus prompt (SQL*Plus: Release 8.1.7.0.0 on SunOS 5.6 Generic_105181-34 sun4u sparc SUNW,Ultra-4): SQL> select count(*) from a_table;

 COUNT(*)
 ----------
     20921

SQL> delete a_table where rownum > 400;

0 rows deleted.

SQL> delete a_table where rownum < 400;

399 rows deleted.

Why is that?

The ROWNUM field is called a "psuedo-column". It really doesn't exist. When you use ROWNUM in your queries, keep in mind that the row numbers are not assigned until the very end of your query. For instance, I can issue the following query:

SELECT ROWNUM, empno FROM emp WHERE empno < 1000;

This may give me the following output:

ROWNUM  EMPNO
------  -----
     1    998
     2    997
     3    901

Now if I order this data, I can get the same employee numbers with different row numbers:

SELECT ROWNUM, empno FROM emp WHERE empno < 1000 ORDER BY empno;

ROWNUM  EMPNO
------  -----
     1    901
     2    997
     3    998

See how the row numbers differ for the same data? And notice what happens when I issue the following query:

SELECT ROWNUM, empno FROM emp WHERE empno > 1000;

ROWNUM  EMPNO
------  -----
     1   1001
     2   1010
     3   1007

The point of this simple exercise is that the row numbers are not the physical ordering of the rows in the table. They are the ordering of the "result set" of your query. So this can make us arrive at the "misleading" results that you've experienced.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close