Hannes,

> typical web app, webinterface to a guestbook.
> I want to show 20 entries of 150 and a butto next 20 and previous 20
> it´s not shure that any of the buttons is clicked but it is shure
> that selection would be made in a different session/transaction.
> how will you do that using minimal DBserver resources without TOP n ?
>

Here's a SQL solution.

Suppose you have

create table t(
k integer not null,
d char(15),
constraint t_pk primary key(k)
);

and some data:

insert into t values (10, 'A');
insert into t values (20, 'B');
insert into t values (30, 'C');
insert into t values (40, 'D');
insert into t values (50, 'E');


you can get the TOP n like this (here n=2):

select * from t data where
(select count(*)
from t control
where data.k > control.k
) < 2;

and the next set like this

select * from t data where
(select count(*)
from t control1
where data.k > control1.k
) >= 2
and
(select count(*)
from t control2
where data.k > control2.k
) < 4;


You can generalize the concept with a SP:

create procedure n_from_t(lower_bound integer, upper_bound integer)
returns (k integer, d char(15))
as
begin
for
select k, d from t data where
(select count(*) from t control1 where data.k > control1.k) >=
:lower_bound
and
(select count(*) from t control2 where data.k > control2.k) <
:upper_bound
into :k, :d
do
suspend;
end


and then do

select * from n_from_t ( 0, 2);
select * from n_from_t ( 2, 4);
etc.


-ST