Coding Tips

It is a common requirement to have pagination in Web applications. The easy way is to query all the records and then apply pagination logic to the data in memory. But this approach won’t work with tables which contains huge data sets.

The best approach is to do the pagination at database level using SQL. Database technology is an old and mature technology and hence this method is the most efficient one.

For example, let us assume that our application contains a CUSTOMER table and we want to display 15 customers at a time in a table. In Oracle, you can write a query like this one,

SELECT * FROM (SELECT C.*,ROWNUM AS R FROM CUSTOMER C) WHERE R > 0 AND R < 10

But what if you want to sort the customer data on the customer name and then want to display the first 10 records?

SELECT * FROM (SELECT C.*,ROWNUM AS R FROM CUSTOMER C ORDER BY CUSTNAME) WHERE R > 0 AND R < 10 

But if you check the results, you will be in for a shock. The data will be wrong. This is because Oracle first applies the ROWNUM limit and then applies sorting on the limited set! So how do we get the first 10 customer after sorting on the customer name? That is where the analytical function RANK() comes to the rescue!

SELECT * FROM (SELECT C.*, (RANK() OVER (ORDER BY CUSTNAME)) AS RN FROM CUSTOMER C)
WHERE RN > 0 AND RN < 10

This approach doesn’t work in databases where there is no built RANK() function. For example, on AS400 systems only way to sort is to navigate to the required data window and then return only the records required. If you want records between 10 and 20, do a query and then navigate from 1 to 10, ignore records and then navigate from 10 to 20 and then break out of the query. But for large data sets navigating to the last page will take a lot of time.