0

Consider this pagination scenario

id name last_name
1 Mickey Mouse
2 Donald Duck
3 Scrooge McDuck
4 Minerva Mouse
5 Goofus Dawg
6 Daisy Duck
SELECT * FROM users
ORDER BY id DESC
LIMIT 3;

The result set is going to be

id name last_name
6 Daisy Duck
5 Goofus Dawg
4 Minerva Mouse

It may make sense from SQL's standpoint, but it makes little sense from the human standpoint. After all, the query was meant as "give me the first page of three in reversed order". "The first page" is clearly the first three rows of the table, not the last three rows so the human way of executing that query would result in

id name last_name
3 Scrooge McDuck
2 Donald Duck
1 Mickey Mouse

The original result set, on the other hand, would be a better fit for this query, "give me the second page of three in reversed order"

SELECT * FROM users
ORDER BY id DESC
LIMIT 3
OFFSET 3;

Can you make Postgres execute ORDER BY after OFFSET and LIMIT?

3
  • The first result does not match the first query. Guess you wanted to add DESC to the sort order there. Jun 4, 2023 at 23:29
  • @ErwinBrandstetter I did, indeed, thank you. I was tired so I forgot to include it Jun 5, 2023 at 2:04
  • 1
    "The first page is clearly the first three rows of the table" there is no first in a table in SQL, it is an unordered set. Unless you use ORDER BY you may get the rows in any order Jun 5, 2023 at 11:33

1 Answer 1

2

You would wrap that into a subquery to display results with a different sort order:

SELECT *
FROM  (
   SELECT *
   FROM   users
   ORDER  BY id
   LIMIT  3
   OFFSET 3
   ) sub
ORDER  BY id DESC;

That said, LIMIT & OFFSET do not scale well for pagination. If your table isn't trivially small, consider a smarter approach. See:

3
  • Also, LIMIT before ORDER BY doesn't make much sense, because the rows in a PostgreSQL table have no fixed ordering. Jun 5, 2023 at 6:22
  • Do you, by any chance, know which Query method would translate into something better than OFFSET (if there's one)? setFirstResult() translates into OFFSET, see. I'm talking JPA Jun 5, 2023 at 13:03
  • @Sergey I can't speak for JPA, sorry. Jun 8, 2023 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.