Recipe11.1.Paginating Through a Result Set

Recipe 11.1. Paginating Through a Result Set

Problem

You want to paginate or "scroll through" a result set. For example, you want to return the first five salaries from table EMP, then the next five, and so forth. Your goal is to allow a user to view five records at a time, scrolling forward with each click of a "Next" button.

Solution

Because there is no concept of first, last, or next in SQL, you must impose order on the rows you are working with. Only by imposing order can you accurately return ranges of records.

DB2, Oracle, and SQL Server

Use the window function ROW_NUMBER OVER to impose order, and specify the window of records that you want returned in your WHERE clause. For example, to return rows 1 through 5:

  select sal   from ( select row_number( ) over (order by sal) as rn,        sal   from emp        ) x  where rn between 1 and 5   SAL ----  800  950 1100 1250 1250 

Then to return rows 6 through 10:

  select sal   from ( select row_number( ) over (order by sal) as rn,        sal   from emp        ) x  where rn between 6 and 10    SAL -----  1300  1500  1600  2450  2850 

You can return any range of rows that you wish simply by changing the WHERE clause of your query.

MySQL and PostgreSQL

Scrolling through a result set is particularly easy due to the LIMIT and OFFSET clauses that these products support. Use LIMIT to specify the number of rows to return, and use OFFSET to specify the number of rows to skip. For example, to return the first five rows in order of salary:

  select sal   from emp  order by sal limit 5 offset 0   SAL -----   800   950  1100  1250  1250 

To return the next group of five rows:

  select sal   from emp  order by sal limit 5 offset 5   SAL -----  1300  1500  1600  2450  2850 

LIMIT and OFFSET not only make the MySQL and PostgreSQL solutions easy to write, but they are quite readable, too.

Discussion

DB2, Oracle, and SQL Server

The window function ROW_NUMBER OVER in inline view X will assign a unique number to each salary (in increasing order starting from 1). Listed below is the result set for inline view X:

  select row_number( ) over (order by sal) as rn,        sal   from emp RN         SAL --  ----------  1         800  2         950  3        1100  4        1250  5        1250  6        1300  7        1500  8        1600  9        2450 10        2850 11        2975 12        3000 13        3000 14        5000 

Once a number has been assigned to a salary, simply pick the range you want to return by specifying values for RN.

For Oracle users, an alternative: you can use ROWNUM instead of ROW NUMBER OVER to generate sequence numbers for the rows:

  select sal   from ( select sal, rownum rn   from ( select sal   from emp  order by sal        )        )  where rn between 6 and 10   SAL -----  1300  1500  1600  2450  2850 

Using ROWNUM forces you into writing an extra level of subquery. The innermost subquery sorts rows by salary. The next outermost subquery applies row numbers to those rows, and, finally, the very outermost SELECT returns the data you are after.

MySQL and PostgreSQL

The OFFSET clause added to the SELECT clause makes scrolling through results intuitive and easy. Specifying OFFSET 0 will start you at the first row, OFFSET 5 at the sixth row, and OFFSET 10 at the eleventh row. The LIMIT clause restricts the number of rows returned. By combining the two clauses you can easily specify where in a result set to start returning rows and how many to return.