Open Stack

On Wed, May 25, 2011 at 1:38 PM, Justin Santa Barbara
<justin at fathomdb.com> wrote:
> The SQL query using the PK would look like this (and there's no need for an
> OFFSET clause):
> SELECT *
> FROM <table>
> ORDER BY <pk>
> WHERE <pk> > @marker
> AND (<additional filters>)
> LIMIT <pagesize>
> Without additional filters, that's a _very_ efficient query for a sensible
> database.

The above isn't actually valid SQL. The WHERE clause must precede the
ORDER BY clause.

In any case, what I meant to say in the original post was that the way
to provide consistent views of a result set is to do:

SET @marker = <TIMESTAMP of initial query>

SELECT * FROM <table>
WHERE updated_at <= @marker
AND <additional filters>
ORDER BY <whatever you want>
LIMIT <pagesize> OFFSET <page param X pagesize>

Cheers,
jay