CODEWITHSUNDEEP
sqlpostgresqlpagination
Mark Fletcher
Mark Fletcher

Reputation: 761

SQL Paginate When Sorting By Two Keys

With a message archive application, I want to paginate through a table of message subjects efficiently. This is easy enough if I sort just by date. I can remember the first and last dates shown on the page and then query for a set of messages greater or less than those. But the application allows some messages to be pinned; they should always appear before other messages, regardless of whether the messages are sorted by ascending or descending date. A message is pinned if a bool is togged true on the record.

What's the best way to efficiently page through this? Assume the table is very large, and messages can be added and deleted at will. We're using Postgres if that makes a difference.

EDIT: I know how to ORDER BY to get what I want. The question is how best to paginate through the results (ie not by using OFFSET).

Upvotes: 1

Views: 651

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248245

Sort the messages with an ORDER BY clause and get the first 50 entries like that:

ORDER BY NOT pinned, create_timestamp LIMIT 50

Remember the last (NOT pinned, create_timestamp) combination.

To retrieve the next page, use

WHERE (NOT pinned, create_timestamp) > (last_notpinned, last_createts)
ORDER BY NOT pinned, create_timestamp LIMIT 50

and so on for the following pages.

You'll need an index like

CREATE INDEX ON tablename ((NOT pinned), create_timestamp);

to make all this efficient.

Upvotes: 2

Related Questions