Reputation: 21
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
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