Page results in [database of your choice]

Question

I'd like to collect the "state of the art" ways to paginate results for any database in this wiki.

Input: I have a huge table PAGE_ME:

create table PAGE_ME (
    ID bigint not null,
    NAME varchar(32) not null,
    CREATED TIMESTAMP not null
)

id is not necessarily in the same order as created. I want to display the results between 5. May 2008 09:03:01 and 3. Aug 2008 11:00:01, 20 at a time, ordered by time, ascending (5. May first). The query should return NAME and CREATED (plus whatever you need to paginate the result), so the inner query is:

select NAME, CREATED
from PAGE_ME
where CREATED between '2008-05-05 09:03:01' and '2008-08-03 11:00:01'
order by CREATED asc

On the keyboards, ready ... Go! ;)

Answer 1

Read the paging queries from my article here and for sql server here. All queries are meant to work on whatever query you throw at them, so no tricks which work only in some situations.

Answer 2

In Oracle a common solution is:

select NAME, CREATED
from
( select NAME, CREATED, ROWNUM rn
  from
  ( select NAME, CREATED
    from PAGE_ME
    where CREATED between '2008-05-05 09:03:01' and '2008-08-03 11:00:01'
    order by CREATED asc
  )
  where ROWNUM <= :max_row
)
where rn >= :min_row

Here, :min_row and :max_row define the limits of the current page, e.g. 1 and 10, 11 and 20, ...