How do I get my query results paginated?

Dear Patrick,

I have got a website with a search form. I want to display a limited number of results to the user and have him/her navigate through different pages. Is this possible using plain SQL?

Kindest regards,

Mitchell Ian

Dear Mitchell,

Of course this is possible. It might take some thinking, but that has never hurt anyone (yet). First we need a table with some randomly sorted data in it. In this example I am just using 20 records, but you can use this approach on bigger tables of course.

[PATRICK]SQL>CREATE TABLE t AS 
             SELECT LEVEL val#, to_char(LEVEL, '9999') value_in_text 
               FROM dual 
             CONNECT BY LEVEL < 21
              ORDER BY dbms_random.random 
             /  

Table created.

The order by dbms_random.random is to ensure the data is inserted in random order. I you just select from this new table then you data will be unordered.

Now we select the first ‘page’ from this table. Our page size is 5 records. So the query will be:

[PATRICK]SQL>SELECT *
              FROM t
             WHERE ROWNUM <= 5
            /
VAL#       VALUE
---------- -----
10         10
20         20
16         16
1          1
17         17

This results in the first 5 rows from the table. If we want to get the next 5, rownums 6 through 10 then you might want to try something like this.

[PATRICK]SQL>SELECT *
               FROM t
              WHERE ROWNUM > 5 AND ROWNUM <= 10
             /

no rows selected

Unfortunately this doesn’t work. I appears this query will never have any resulting row with a number between 6 and 10. The solution to this issue is the use of a subquery:

[PATRICK]SQL>SELECT val#, value_in_text
               FROM (SELECT t.val#, t.value_in_text, ROWNUM rn
                       FROM t)
              WHERE rn > 5 AND rn <= 10
             /

VAL#       VALUE
---------- -----
13         13
4          4
5          5
3          3
14         14

In this query we first select all the rows we might need for the pages and using this resultset we just select the rows we are interested in for our page.

If your table is rather big you may want to include the maximum rownum in the inline view.

[PATRICK]SQL>SELECT val#, value_in_text
               FROM (SELECT t.val#, t.value_in_text, ROWNUM rn
                       FROM t
                      WHERE ROWNUM <= 10)
              WHERE rn > 5 AND rn <= 10
             /

VAL#       VALUE
---------- -----
13         13
4          4
5          5
3          3
14         14

As you are probably aware of the is no guarantee on how the rows are being returned unless you specify an order by clause. But what happens if you were to just include this order by in your query. Let’s see what happens when you include it in the first query for the first page:

[PATRICK]SQL>SELECT *
               FROM t
              WHERE ROWNUM <= 5
             ORDER BY t.val#
             /

VAL#       VALUE
---------- -----
12         12
13         13
15         15
17         17
19         19

The rows returned are in order, but they are definitely not the first 5 values currently in the table. That is how the sql engine works. It first gets the first 5 rows to honor the predicate in the query and then it sorts the result before returning it to the caller.

What we should do to get the correct behavior of our query is use a subquery to get the results in order and apply the rownum clause to that result.

[PATRICK]SQL>SELECT *
               FROM (SELECT *
                       FROM t
                     ORDER BY t.val#)
              WHERE ROWNUM &lt;= 5
             /

VAL#       VALUE
---------- -----
1          1
2          2
3          3
4          4
5          5

We can now use this to build a query to get the next page of results:

[PATRICK]SQL>SELECT val#, value_in_text
               FROM (SELECT val#, value_in_text, ROWNUM rn
                       FROM (SELECT *
                               FROM t
                             ORDER BY t.val#)
                     ORDER BY rn)
              WHERE rn > 5 AND rn <= 10
             /

VAL#       VALUE
---------- -----
6          6
7          7
8          8
9          9
10         10

When you have access to an Oracle 12c database, it is a lot easier, to get the first page of the ordered results, you can issue this statement:

[PATRICK]SQL>SELECT *
               FROM t
            ORDER BY t.val#
            FETCH FIRST 5 ROWS ONLY
            /

To get another page you can provide query with an offset of how many rows to skip:

[PATRICK]SQL>SELECT *
               FROM t
             ORDER BY t.val#
             OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY
             /

Under the covers Oracle still issues similar queries as the ones we built earlier, but it is a lot easier to write these.

Hope this sheds a bit of light on your issue.

Happy Oracle’ing,

Patrick Barel

If you have a question you want answered, please send an email to patrick[at]bar-solutions[dot]com. If I know the answer, or can find it for you, maybe I can help.

This question has been published in OTech Magazine of Summer 2014