EXPERT RESPONSE
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.
|
