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

Question

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?

The first result does not match the first query. Guess you wanted to add DESC to the sort order there. — Erwin Brandstetter, Jun 04 '23 at 23:29
@ErwinBrandstetter I did, indeed, thank you. I was tired so I forgot to include it — Sergey Zolotarev, Jun 05 '23 at 02:04
"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 — Charlieface, Jun 05 '23 at 11:33

score 2 · Accepted Answer · answered Jun 04 '23 at 23:26

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:

answered Jun 04 '23 at 23:26

Erwin Brandstetter

175,982
27
439
600

Also, LIMIT before ORDER BY doesn't make much sense, because the rows in a PostgreSQL table have no fixed ordering. – Laurenz Albe Jun 05 '23 at 06: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 – Sergey Zolotarev Jun 05 '23 at 13:03
@Sergey I can't speak for JPA, sorry. – Erwin Brandstetter Jun 08 '23 at 21:58

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

1 Answers1