Oracle Tips and Tricks — David Fitzjarrell

June 17, 2008

Row, Row, Row

Filed under: General — dfitzjarrell @ 13:41

Many times I’ve seen questions similar to the following:

“I want the 17th row from the table. How do I do that?”

which says much as to the level of misunderstanding people have regarding the standard Oracle table type, a heap table. Such tables have no order, thus it’s really impossible to return the 17th row of a given table and be able to do it repeatably with large data sets. What confuses people about this is the fact that Oracle provides a pseudo-column named ROWNUM which sequentially numbers a result set. Let’s look at this interesting element of Oracle and see how it works, what it will work for and what it won’t do.

ROWNUM is, as mentioned previously, a sequential number assigned to elements of a result set, which means you have to start returning data before you can assign a ROWNUM of 1. Queries which will work are:

SQL> select rownum, object_name
  2  from user_objects
  3  where rownum < 16
  4  /

    ROWNUM OBJECT_NAME
---------- -----------------------
         1 ADMIN_EXT_EMPLOYEES
         2 ATTAINMENTS
         3 ATTAINMENTS_PK
         4 BONUS
         5 CHECK_SAL
         6 CHGCOLTEST
         7 CPU_APPLY_VERSION
         8 CPU_APPLY_VERSION_HOLD
         9 CPU_APPLY_VERSION_PK
        10 CPU_APPLY_VERSION_SAVE
        11 CUSTOMERS_LOOKUP

    ROWNUM OBJECT_NAME
---------- -----------------------
        12 CUSTOMERS_LOOKUP_PRIM
        13 CUSTOMERS_LOOKUP_ZE
        14 DATES_PKG
        15 DATES_PKG

15 rows selected.

SQL>

Notice we stopped returning rows when ROWNUM reached 15; since we used an inequality in our WHERE condition we were able to start with 1 and count up to our limit. Of course you can simply return the ROWNUM for every row in a result set:

SQL> select rownum, ename
  2  from emp;

    ROWNUM ENAME
---------- ----------
         1 SMITH
         2 ALLEN
         3 WARD
         4 JONES
         5 MARTIN
         6 BLAKE
         7 CLARK
         8 SCOTT
         9 KING
        10 TURNER
        11 ADAMS

    ROWNUM ENAME
---------- ----------
        12 JAMES
        13 FORD
        14 MILLER

14 rows selected.

SQL>

Since we’re dealing with heap tables there is no order to the data, and if we want the data above ordered by ename something interesting happens to ROWNUM:

SQL> select rownum, ename
  2  from emp
  3  order by ename;

    ROWNUM ENAME
---------- ----------
        11 ADAMS
         2 ALLEN
         6 BLAKE
         7 CLARK
        13 FORD
        12 JAMES
         4 JONES
         9 KING
         5 MARTIN
        14 MILLER
         8 SCOTT

    ROWNUM ENAME
---------- ----------
         1 SMITH
        10 TURNER
         3 WARD

14 rows selected.

SQL>

ROWNUM is assigned as the records are retrieved, before they are ordered by the query, so ordering the data by ename caused the ROWNUM values to display out of sequence. We can produce a result set where both the ename values and the ROWNUM values are ‘properly’ ordered, but it takes a trick — selecting from an ordered subquery then assigning the ROWNUM values to the ordered names:

SQL> select rownum, x.ename
  2  from
  3  (select ename
  4  from emp
  5  order by ename) x
  6  /

    ROWNUM ENAME
---------- ----------
         1 ADAMS
         2 ALLEN
         3 BLAKE
         4 CLARK
         5 FORD
         6 JAMES
         7 JONES
         8 KING
         9 MARTIN
        10 MILLER
        11 SCOTT

    ROWNUM ENAME
---------- ----------
        12 SMITH
        13 TURNER
        14 WARD

14 rows selected.

SQL>

I expect that query answers the question ‘how to serial rows in oracle’, unless the questioner is really asking in regard to some other topic with that rather ambiguous interrogatory.

Okay, so I can see the ROWNUM value for each row returned, can I restrict the results to only one row of my choosing? Well, sort of … but it takes yet another ‘trick’:

SQL> select *
  2  from (select rownum rn, x.ename
  3  from
  4  (select ename
  5  from emp
  6  order by ename) x)
  7  where rn = 13;

        RN ENAME
---------- ----------
        13 TURNER

SQL>

We used the ordered list query to do this, but ordered data isn’t a requirement. The two requirements are:

1) select the ROWNUM along with the rest of the data we want using a subquery, and alias the ROWNUM pseudo-column so we can reference its value later

2) use the above select as a table for our final select, and restrict the results to a given ROWNUM value (in this case aliased as rn)

As if by magic we return only the 13th record of the result set. Remember that this does NOT necessarily return the 13th row of the table (and with a heap table there is no order to the data within, so there is no ’13th’ row in the actual data). The subquery construction is necessary so we can return ROWNUM values for each row of the table data and then use that assignment to restrict the output to the desired element. Had we tried to access, directly, the ROWNUM of 13 without the trick I just ilustrated we would return … nothing:

SQL> select rownum, ename
  2  from emp
  3  where rownum = 13;

no rows selected

SQL>

Since no row met the criteria of ROWNUM = 13 (because we never were able to assign a ROWNUM of 1 so the counter never was able to increase) nothing was returned. ROWNUM is assigned to every record that meets the query criteria; the criteria we provided, ROWNUM = 13, was impossible to meet because we were asking for a value we could never reach. In pseudo-code the decision tree looks like this:

  ---------------------------> Does this row meet our criteria?
 ^                                 
 |                                         / \
 |                                        /   \
 ^                                      Yes    No
 |                                   
 |                      Return data and ctr    Leave ctr untouched
 |                      Increment ctr          Return no data
 ^                                   
 |                                       \    /
 |                                        \  /
 |                                 
 <----------------------------------------

Since the criteria could not be met the counter could not increase and no data was displayed.

Can a range of rows be returned that doesn’t start with ROWNUM=1? Yes, simply modify the above working query to use BETWEEN instead of ‘=’:

SQL> select *
  2  from (select rownum rn, x.ename
  3  from
  4  (select ename
  5  from emp
  6  order by ename) x)
  7  where rn between 2 and 11;

        RN ENAME
---------- ----------
         2 ALLEN
         3 BLAKE
         4 CLARK
         5 FORD
         6 JAMES
         7 JONES
         8 KING
         9 MARTIN
        10 MILLER
        11 SCOTT

10 rows selected.

SQL>

Knowing how ROWNUM operates is key to using it properly. Hopefully these examples will clear the confusion ROWNUM has generated over the years and help you return the results you want.

Advertisements

1 Comment »

  1. […] Since ROWNUM can never assign the value of 1 it never gets to your desired value of 44 and, unfortunately, never returns any data. There is a ‘workaround’ for this, but it sidetracks the discussion on date strings and I’ll save it for another post. […]

    Pingback by How About A Date? « Oracle Tips and Tricks — July 22, 2012 @ 22:08 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: