Page results in [database of your choice]
I would like to collect "modern" ways of paging results for any database on this wiki.
Login: I have a huge PAGE_ME table:
create table PAGE_ME (
ID bigint not null,
NAME varchar(32) not null,
CREATED TIMESTAMP not null
)
id
not necessarily in the same order as created
. I want to show the results between 5. May 2008 09:03:01
and 3. Aug 2008 11:00:01
, 20 at a time, ordered by time, in ascending order (first May 5.). The query should return NAME
and created
(plus whatever you need to break into 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
The keyboards are ready ... Go!;)
a source to share
Read swap queries from my article here and for sql here . All requests are designed to work on whatever request you throw at them, so no tricks that only work in some situations.
a source to share
In Oracle, the general 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, eg. 1 and 10, 11 and 20, ...
a source to share