May 27, 2005

SQL for retreiving rows in a Paging mode

The following statements can be useful which developing a Page or Screen which shows users X rows per page and allows the user to navigate to previous or next page (Paging).

These SQLs can be used to retrieve only the required minimum rows from the database. However, the syntax is vendor dependent.

In the following examples, the query retreives only a sub set of employees (40th - 50th logical rows) ordered by name.

SQLServer: (Source: MSDN Documentation)
First selects the top 50 rows ordered by the name. Then orders that result in descending order and gets the top 10, giving the rows from 40 to 50. NOTE: The results are in descending order.
      FROM emp
      ORDER BY emp_name ASC) Table1
ORDER BY emp_name DESC

Oracle: (Source:
First orders the table by name, then retrieves the top 50 rows. Then uses the retrieved rownum value to get all rows equal to or greater than 40.
FROM (SELECT A.*, rownum r from (SELECT * FROM emp order by emp_name) A 
      WHERE rownum < 50)
WHERE r >= 40

As far as I am concerned, these statements only avoid the processing on the client side. The database will still have to process incremental amount of data especially as the user navigates towards the last page.