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

Ordering of rows or columns in a relational table

How can we retrieve the 10th record from a table through the SQL prompt? When I write "select rownum from table" the table gives me rownum but when I write "select * from table where rownum=10" it says "no rows selected". Can you please guide me in solving this problem?

According to Relational Database theory initially proposed by Dr. Codd, there is no ordering to rows or columns in a relational table. This means that what is the 10th record this time is not guaranteed to be the 10th record next time. But humans don't like to think that way. So this question comes up all the time.

In order to understand the solution, we need to understand the process a little. Let's suppose that I issue the following query:

ORA9I SQL> select rownum,owner,object_name from objects
  2  where rownum < 10;

    ROWNUM OWNER      OBJECT_NAME
---------- ---------- ------------------------------
         1 SYS        ACCESS$
         2 SYS        AGGXMLIMP
         3 SYS        AGGXMLIMP
         4 SYS        AGGXMLINPUTTYPE
         5 SYS        ALL_ALL_TABLES
         6 SYS        ALL_ARGUMENTS
         7 SYS        ALL_ASSOCIATIONS
         8 SYS        ALL_AUDIT_POLICIES
         9 SYS        ALL_BASE_TABLE_MVIEWS

9 rows selected.

This gives me the first 9 rows of the table. But what if I change the query slightly? For example:

ORA9I SQL> select rownum,owner,object_name from objects
  2  where owner='SYSTEM' and rownum < 10;

    ROWNUM OWNER      OBJECT_NAME
---------- ---------- ------------------------------
         1 SYSTEM     AQ$DEF$_AQCALL
         2 SYSTEM     AQ$DEF$_AQERROR
         3 SYSTEM     AQ$_DEF$_AQCALL_E
         4 SYSTEM     AQ$_DEF$_AQERROR_E
         5 SYSTEM     AQ$_INTERNET_AGENTS
         6 SYSTEM     AQ$_INTERNET_AGENT_PRIVS
         7 SYSTEM     AQ$_QUEUES
         8 SYSTEM     AQ$_QUEUES_CHECK
         9 SYSTEM     AQ$_QUEUES_PRIMARY

9 rows selected.

How can an object owned by SYSTEM have ROWNUM=1 when I've already shown that the first row in the table is an object owned by SYS?!?! That's because the ROWNUM "psuedo-field" is only assigned after the result set of the query is known. In the first example, I returned *all* rows of the table. The WHERE ROWNUM < 10 clause limited my output to the first 9 rows out of all rows of the table. But look at the second query closely. It is asking for only those rows where the owner is SYSTEM. Now out of that result set, return the first 9 rows. So ROWNUM is a psuedo column that is only defined after the result set is determined.

Now to answer your question... How do you return a specific row? Let's say I want to return the 2nd row out of this table:

ORA9I SQL> select * from users;

USERNAME      USER_ID CREATED
---------- ---------- ---------
OUTLN              11 27-JUN-01
DBSNMP             17 27-JUN-01
PEASLAND           18 24-AUG-01

I have to issue a query like the following:

ORA9I SQL> select username,user_id,created
  2  from (select rownum r,username,user_id,created from users)
  3* where r=2;

USERNAME      USER_ID CREATED
---------- ---------- ---------
DBSNMP             17 27-JUN-01

This gets the rownum from the table first, aliases it, and then uses that result in the where clause.

For More Information

  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

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