sqlmysqlselectpagination

How can I write a SELECT query with starting row of particular PK?


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

Solution

  • 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!