Sorting issues for Pagination sorting in Postgresql

Question

For pagination sorting am geting duplicated data once load the next page. For Eg: if a we have full name: Ahern in 1st page and after moving next page also sometimes we can able to see the same record. I need accurate data for each time for sorting a specific field. Am using order by as : ORDER BY Hourse :: numeric ASC LIMIT 50 OFFSET 50

Please suggest an accurate sorting method using in postgres sql.

Semjon · Accepted Answer · 2016-06-13 07:25:13Z

4

In order to use SQL LIMIT ... OFFSET ... as pagination for PostgreSQL you need to add ordering by unique value. Seems Hourse :: numeric is not unique over the table.

If you have an unique ID column, I would recommend to add this column as a second ordering argument, like ORDER BY Hourse :: numeric ASC, id ASC

PS. There is a brilliant article about Postgresql pagination tricks https://www.citusdata.com/blog/1872-joe-nelson/409-five-ways-paginate-postgres-basic-exotic

answered Jun 13, 2016 at 7:25

Semjon

1,0237 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sorting issues for Pagination sorting in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related