At 02:09 PM 25-06-00 +0200, you wrote:

>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 ?
>
>hannes hernler

TOP 20 doesn't allow you to do this (fetching groups of 20 entries, etc.),
it only lets you fetch the first 20 rows.
So, no matter how you go about fetching the first n rows, you are going to
have to use some client input to make it so you fetch the group of n
records you want (because the server won't know which rows it already sent
you).

The basic routine involves first designing a set that gets the rows in some
sort of order, that determines "TOP n of what?" Oldest? Youngest? Highest
primary key? Lowest primary key?

Let's say you want you want to pick out groups of guests, starting with the
oldest (assuming you have all their birthdays and the column is
indexed)...if you want from the youngest to oldest, you'll need a
descending index on birthday...)

create procedure TOP_N(n integer, LastID integer)
returning (sFirstName varchar(30), sLastName varchar(30), dBirthday date)
as
declare variable iCounter integer;
declare variable bOK char(1);
declare variable bOKtoGo char(1);
begin
iCounter = 0;
bOK = 'F';
bOKtoGo = 'F';
for select FirstName, LastName, Birthday from GuestBook
where dBirthday > :StartingValue
order by Birthday /* indexed */
into :sFirstName, :sLastName, :dBirthday do
begin
if (Not (bOK='T')) then
if GuestBook.ID = :LastID then bOK='T'; /* the final row from the
previous set */
if (bOKtoGo='T') then
begin
suspend;
iCounter = :iCounter + 1;
if iCounter = :n then Exit;
end
if bOK='T' then bOKtoGo='T'; /* we have gone past the last row of the
previous set */
end

This sample really just demonstrates how to get Top(n) from a certain
starting point in an ordered dataset. Top(n) isn't necessarily going to be
the best way to go for a web interface. If you have a stateful web
application, like WebHub, you can probably economise on database i/o by
holding your dataset on the web server and using the methods of your web
application to control which records to display. InternetXpress (Delphi
5), while not stateful, gives you a data provider layer where you can
control which rows go out in response to an http request.

Helen
http://www.interbase2000.org
___________________________________________________
"Ask not what your free, open-source database can do for you,
but what you can do for your free, open-source database."
(J.F.K.)