--- In IBDI@y..., Lester Caine <lester@l...> wrote:

> Just to muddy the waters.
>
> I can see the need for FIRST n, and it would help me with a
> number of routines, but what about AFTER <bookmark> rather
> than between some arbitary numbers..

You obviously mean selecting the records in the result set where the
index of the records is larger than the index of the row that
contains the unique key equal to the given bookmark. This would look
like:

SELECT ...... WHERE keyfield AFTER bookmark

This may be useful for getting to the "next" page when dealing with
queries returning one unique field. But it cannot be used when the
unique key of the query is a combination of two or more fields (as in
the database example that Ann used). Also, the RDBM uses an
internally generated index for the base query (the one without the
AFTER clause) to decide from which row it should return the results:
i.e. a ROWNUM!

> This I think ties up with what Jason suggested earlier. Once
> you have displayed the first 10 records, you get the next
> ten by looking 'after' the bookmark of the last record
> displayed. This has the advantage that if one or more of the
> first n records are deleted, then a continuous display of
> information is maintained.

It has been discussed what happens when the bookmark record is
deleted. Jason proposed a database design where "deleted" rows are
just marked as deleted, which is fine for many purposes.

> WHERE x > <bookmark> would work if the database is designed
> right in the first place, so that all this talk of ROWNUM is
> not required. I view ROWNUM in the same light as GOTO - the
> lasy way of programming. Design things right in the first
> place and you do not need either.

However, the ROWNUM and LIMIT debate is not about selecting the next
set of records from the page you are on. It is about random access to
pages. Nobody has proposed a working solution, based on existing
capabilities, that works without middleware or clumsy stored
procedures. An AFTER keyword does not answer this need. Your "lasy"
(sic) label does not help either.

Fred Toussi