Programmer's Cookbook

I learned a very interesting thing about Oracle's ROWNUM variable behaves and how it affects queries that have on ORDER BY clause.

My goal was to create a Java Servlet that showed query results in a list format, with links to more pages of results and PREV and NEXT links - much like a search engine. For example, if there were 100 results, and 10 results per page, there would be PREV and NEXT links around links for the individual pages ( 1 2 3 4 5 6 7 8 9 10). Something we've all seen before.

Not seeing any problem with Oracle's ROWNUM, my basic thinking was to pass a START and END to the Servlet, which would dynamically create my queries and they would basically look like this:

SELECT * from table where rownum between <START> and <END>

For example, the first page results query would be:

SELECT * from table where rownum between 1 and 5

The next page would be:

SELECT * from table where rownum between 6 and 10

and so on.

What I didn't realize is that ROWNUM cannot be used this way because ROWNUM doesn't behave as you think it does, and it cannot be used reliably with any ORDER BY clause. See below

Let's start by getting a known set of results, in this case there are 10 results for employees with last name "smith". Note the value of ROWNUM

SQL> select first_name, last_name, rownum 
        from employee 
        where last_name = 'SMITH';

FIRST_NAME      LAST_NAME                          ROWNUM
--------------- ------------------------------ ----------
CLARA           SMITH                                 1
DEBORAH         SMITH                                 2 
GERTRUDE        SMITH                                 3 
JEFF            SMITH                                 4 
KEITH           SMITH                                 5 
KENN            SMITH                                 6 
MARK            SMITH                                 7 
MARYLYNN        SMITH                                 8 
MATTHEW         SMITH                                 9 
NICOLE          SMITH                                10 

10 rows selected.

Great! That looks fine ... 1 through 10, they're all in order. Let's try getting just the first 5 results.

SQL> select first_name, last_name, rownum 
        from employee 
        where rownum between 1 and 5 and 
        last_name = 'SMITH';

FIRST_NAME      LAST_NAME                          ROWNUM 
--------------- ------------------------------ ----------
CLARA           SMITH                                 1 
DEBORAH         SMITH                                 2 
GERTRUDE        SMITH                                 3 
JEFF            SMITH                                 4 
KEITH           SMITH                                 5 

5 rows selected.

Still looks good. In reality though, i want to be able to change how the results are sorted, so i create a dynamic ORDER BY clause. In this example, let me use LOCATION:

SQL> select first_name, last_name, location, rownum 
from employee 
where last_name = 'SMITH' 
order by location;

FIRST_NAME      LAST_NAME                    LOC     ROWNUM 
--------------- ---------------------------- ----- ---------- 
CLARA           SMITH                        211            1 
JEFF            SMITH                        329            6 
GERTRUDE        SMITH                        335            4 
MARYLYNN        SMITH                        335            8 
KEITH           SMITH                        348            5 
MARK            SMITH                        349            7 
MATTHEW         SMITH                        450            9 
NICOLE          SMITH                        450           10 
DEBORAH         SMITH                        813            3
KENN            SMITH                        847            2 

10 rows selected.

Holy cow! Why is my ROWNUM out of order? Anyway, let me see if my ROWNUM filtering clause still works for results 1 - 5. I should expect to see CLARA, JEFF, GERTRUDE, MARYLYNN, and KEITH as my results, in order.

SQL> select first_name, last_name, location, rownum 
from employee 
where rownum between 1 and 5 and 
  last_name = 'SMITH' 
order by location;

FIRST_NAME      LAST_NAME                      LOC     ROWNUM
--------------- ------------------------------ ----- ---------- 
CLARA           SMITH                        211            1 
GERTRUDE        SMITH                        335            4 
KEITH           SMITH                        348            5 
DEBORAH         SMITH                        813            3 
KENN            SMITH                        847            2 

5 rows selected.

OH NO! This isn't going to work! From my research, ROWNUM is a value that is inherent to that row of data, and *not* a value that is computed on the fly. WHAT A BUMMER!

From some more research, I found a trick that is 100% effective and does not have any performance impact.

What you have to do is embed your original query with NO USE OF ROWNUM in 2 embedded SQL calls

SQL> select * from (
       SELECT  x.*, rownum as r FROM (
           select first_name, last_name, location, rownum 
         from employee 
         where last_name = 'SMITH' 
         order by location
       ) x
     )
     where r between 1 AND 5;

FIRST_NAME      LAST_NAME                      LOC     ROWNUM          R 
--------------- ------------------------------ ----- ---------- ---------- 
CLARA           SMITH                        211            1          1 
JEFF            SMITH                        329            6          2 
GERTRUDE        SMITH                        335            4          3 
MARYLYNN        SMITH                        335            8          4 
KEITH           SMITH                        348            5          5 

5 rows selected.

These are the results I expected in the first place when re-ordering by location.

And as an aside, MYSQL offers the LIMIT BY clause which works with the behavior you'd expect, without having to jump through hoops to get the subset you want.

Example:

select first_name, last_name, location, rownum 
from employee 
where 
   rownum between 1 and 5 and 
   last_name = 'SMITH' 
LIMIT BY 1, 5 
order by location;

This would return me exactly what I want. Makes you wonder why ORACLE is so expensive and doesn't offer this seemingly intuitive functionality.