I'm using MySQL, which support OFFSET and LIMIT, it's great. But what if I want query with first row equal to particular primary key instead of page index?
e.g. I have a table:
ID NAME HEIGHT
1 kevin 1.67
2 bob 1.82
3 jane 1.70
4 wayne 1.59
I want a list of people order by their height ASC with first page's last ID is 1, the whole view is 4 1 3 2 (page size = 2)
Something should like:
SELECT ID,NAME,HEIGHT FROM table OFFSET ???? LIMIT 2 ORDER BY HEIGHT ASC
So I should get:
3 jane 1.70
2 bob 1.82
Just found the solution.
The request has to include both id
and a sorting factor (in my case it's height
). so that the query would look like this:
SELECT * FROM table WHERE (t.height = last_height AND t.id > last_id) OR t.height > last_height ORDER BY t.height ASC, t.id ASC LIMIT 2
In case there are multiple people with the same height, the id
can be used to pin-point the starting row.
Many thanks to Clockwork-Muse for lighting the bulb!