CODEWITHSUNDEEP
sqlpostgresqlsortingpagination
Davis Mariotti
Davis Mariotti

Reputation: 605

Is there a way to select results after a certain id in an order list?

I'm trying to implement a cursor-based paginating list based off of data from a Postgres database.

As an example, say I have a table with the following columns:

id | firstname | lastname

I want to paginate this data, which would be pretty simple if I only ever wanted to sort it by the id, but in my case, I want the option to sort by last name, and there's guaranteed to be multiple people with the same last name.

If I have a select statement like follows:

SELECT * FROM people
ORDER BY lastname ASC;

In the case, I could make my encoded cursor contain information about the lastname so I could pick up where I left off, but since there will be multiple users with the same last name, this will be buggy. Is there a way in SQL to only get the results after a certain id in an ordered list where it is not the column by which the results are sorted?

Example results from the select statement:

1 | John  | Doe
4 | John  | Price
2 | Joe   | White
6 | Jim   | White
3 | Sam   | White
5 | Sally | Young

If I wanted a page size of 3, I couldn't add WHERE lastname <= :lastname as I'd have duplicate data on the list since it would return ids 2, 6, and 3 during that call. In my case, it'd be helpful if I could add to my query something similar to AFTER id = 6 where it could skip everything until it finds that id in the ordered list.

Upvotes: 0

Views: 1960

Answers (2)

Abdel P.
Abdel P.

Reputation: 738

Limit and offset are used for pagination e.g.:

SELECT id, lastname, firstname FROM people Order by lastname, firstname, id Offset 0 Limit 10 This will bring you the first to the 10th row, to retrieve the next page you need to specify the offset to 10

Here the documentation: https://www.postgresql.org/docs/9.6/static/queries-limit.html

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Yes. If I understand correctly:

select t.*
from t
where (lastname, id) > (select t2.lastname, t2.id
                        from t t2
                        where t2.id = ?
                       )
order by t.lastname;

I think I would add firstname into the mix, but it is the same idea.

Upvotes: 1

Related Questions