Paging In Oracle

Oracle doesn't have the LIMIT keyword like MySQL. So, how can we do paging in Oracle? There is a little trick to do it.

Let's assume there is a table named table1 and it has two fields such as trans_id and user. When we execute the SELECT statement, the result will be like this:

SELECT trans_id, user FROM table1 ORDER BY trans_id;

TRANS_ID    USER
----------- ------
080003      001   
080003      001   
080003      001   
080005      001   
080005      001   
080024      002   
080024      002   
080025      001   
080151      001   
080151      001   
080296      001   
080296      001   
080413      003   
080427      ADMIN

First step to do the paging is we will use that result as a table. The trick will start here. We're going to add a new field to display the row number for the result. The row number will be our key to do the paging. So, the query and its result will be like this:

SELECT rownum rn, t1.* FROM (SELECT trans_id, user FROM table1
ORDER BY trans_id) t1 ;

   RN TRANS_ID    USER
----- ----------- ------
    1 080003      001 
    2 080003      001 
    3 080003      001 
    4 080005      001 
    5 080005      001 
    6 080024      002 
    7 080024      002 
    8 080025      001 
    9 080151      001 
   10 080151      001 
   11 080296      001 
   12 080296      001 
   13 080413      003 
   14 080427      ADMIN

Next step. Based on the result, we will use it as a table. Then use the rn field to do the paging. For example we're going to show only the top five of the result. So, our query will be like this :

SELECT * FROM (SELECT rownum rn, t1.* FROM (SELECT trans_id, user FROM table1
ORDER BY trans_id) t1) t2 WHERE rn BETWEEN 1 AND 5;

   RN TRANS_ID    USER
----- ----------- ------
    1 080003      001 
    2 080003      001 
    3 080003      001 
    4 080005      001 
    5 080005      001