Sunday, March 5, 2017

Oracle: how to page / paging result in select

 

If You want to to a fast paging in your application the best way is sometimes to let the database do the job. You could of course let the appication server select all rows and then pick out the one you want but that could be a huge job if the query gives a extensive recordset.

Paging in Oracle is not as easy as one could think. If you would like your query to return lets say 100 rows with the start at row 1300 you could use the following SQL statement:


select * from (
  select rowum as rn, b.*
    from (select a.columnName1, a.columnName2
            from tableName a
           order by a.columnName1) b
        ) c
    where c.rn between 1300 and 1300 + 100;

Have a nice day and happy SQL-hacking!