CODEWITHSUNDEEP
postgresqlpostgresql-9.1postgresql-9.3postgresql-9.2rails-postgresql
Kannan
Kannan

Reputation: 21

Sorting issues for Pagination sorting in Postgresql

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.

Upvotes: 0

Views: 2290

Answers (1)

Semjon
Semjon

Reputation: 1023

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

Upvotes: 4

Related Questions