Hi,

Let say I have a table containing 100 student records:

CREATE TABLE students (
  id SERIAL,
  name TEXT NOT NULL,
  CONSTRAINT students_pk PRIMARY KEY (id)
);

It is easy to query for "next page" with keyset pagination. For example, if I want to get 10 rows of student records after reading student records #10 to #20:

SELECT id, name
FROM students
WHERE id > 20
ORDER BY id ASC
LIMIT 10;

But how to query for "previous page"? Let say I'm reading student records #20 to #30, now I want to get 10 student records before the current set. Here is how I would do it:

SELECT id, name
FROM (
  SELECT id, name
  FROM students
  WHERE id < 20
  ORDER BY id DESC
  LIMIT 10
) AS t
ORDER BY id ASC;

In the sub-query, I sort the student records by the student ID in descending order, then I get 10 rows of student record, and finally sort those 10 student records by student ID in ascending order.

Is it a good way to do it? Is there any way to avoid doing the sorting twice?

Many thanks.