It gets worse when you want to allow sorting the paginated results by something other than the integer primary key, such as a name. Duplicate names with differe

It gets worse when you want to allow sorting the paginated results by something other than the integer primary key, such as a name. Duplicate names with different ids means having to use GROUP BY. I typed out the example queries below from memory, hopefully I haven't made any logic errors. Note: a) Requires additional indexes to make it efficient. b) Accounts for inserted and deleted rows. c) You cannot use page numbers. You can only do first page, next and previous pages, and last page. d) Example is for 20 items per page. The query pulls 21 (+1) items, so app can determine whether there is a next or previous page. e) In a web app or api for example, your parameters get crazy, such as: /users?page=next&sort=name&sortId=20&sortVal=George -- first page SELECT name, id FROM users GROUP BY name, id ORDER BY name, id LIMIT 21; -- next page (ex: last item on current -- page has id 20 and name 'George') SELECT name, id FROM users WHERE name >= 'George' AND id > 20 GROUP BY name, id ORDER BY name, id LIMIT 21; -- prev page (ex: first item on current -- page has id 21 and name 'Harry') SELECT name, id FROM users WHERE name <= 'Harry' AND id < 21 GROUP BY name, id -- need DESC, reverse order in app for display ORDER BY name DESC, id DESC LIMIT 21; -- last page SELECT name, id FROM users GROUP BY name, id -- need DESC, reverse order in app for display ORDER BY name DESC, id DESC LIMIT 21;