I am working with cursor pagination and sorting right now, for example you can paginate with cursor but you might also want it to be sorted in a certain field and direction for a api. ie. api/?cursor=1&sort_by=(id, asc)

My main discussion is around should the sorting change what your pagination key is? So let's suppose i am using id as the pagination key, and then I also want to sort by created_date desc, so is it better to do

Select * from table where (created_date, id) < (x, y) order by created_date desc, id desc or Select * from table where (id) < (y) order by created_date desc, id desc

The first would create a new list base on the new pagination key The second option always keep the same list but sort it later