SQL Query For Pagination on Database Side

Recently, i was tasked with implementing pagination for a data-grid in my application on the server-side. The pagination which i choose, is enabled at the database end by using SQL. After a bit of google reasearch, i stumbled upon the following query which seems to fit the bill.

select * 
  from ( select /*+ FIRST_ROWS(n) */ 
  a.*, ROWNUM rnum 
      from ( your_query_goes_here, 
      with order by ) a 
      where ROWNUM <= 
      :MAX_ROW_TO_FETCH ) 
where rnum  >= :MIN_ROW_TO_FETCH;

where
FIRST_ROWS(N) tells the optimizer, “Hey, I’m interested in getting the first rows, and I’ll get N of them as fast as possible.”
:MAX_ROW_TO_FETCH is set to the last row of the result set to fetch—if you wanted rows 50 to 60 of the result set, you would set this to 60.
:MIN_ROW_TO_FETCH is set to the first row of the result set to fetch, so to get rows 50 to 60, you would set this to 50.
Courtesy : Ask Tom, Q & A

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s