Mysql – How to get rows after a specific row, from a resultset of a specific sort order

mysql-5.6

I have a table that has an ID column of type varchar(255) as the primary key. The rest of the columns comprise of text, datetime and varchar(255) types. Let's consider the given query:

SELECT ID from `table` 
where (certain condition)    
order by    
    columnX,
    columnY,
    ID
limit 10
;

The ORDER BY clause has the specified columns sorted in an ascending order for this example, but it could contain mixed directions (both ASC & DESC). Anyway, executing the above query gives me a resultset like this:

27087675-38b5-41ea-afa5-9cf24c388545
770a933d-064f-41f5-8d2a-55acaedf38bf
a2e509e3-3a61-44e6-a36e-955d7b1f7b5e
19c47c26-075f-4761-a462-02025f4b29ce
ab3418ee-0b26-40ec-87b8-2b3636bb4e59
24caf7cb-fca5-45f0-81a0-f97175d8e071
285295fb-4ba7-4a70-9aaa-0a93cac058e8
f976e378-394f-42ed-af07-012336660546
5c0201c4-4aec-4e48-95c8-91e73b7843ee
de8f6adf-f880-4758-aeda-9e66a308bbc0

The resultset consists of 10 elements (for the sake of brevity). Now, what I'm trying to achieve, is to get the elements after a certain ID, in the same order. For example, if I want to get the elements after ab3418ee-0b26-40ec-87b8-2b3636bb4e59, the output should be

24caf7cb-fca5-45f0-81a0-f97175d8e071
285295fb-4ba7-4a70-9aaa-0a93cac058e8
f976e378-394f-42ed-af07-012336660546
5c0201c4-4aec-4e48-95c8-91e73b7843ee
de8f6adf-f880-4758-aeda-9e66a308bbc0

What I tried:

  • I tried the condition AND ID > 'ab3418ee-0b26-40ec-87b8-2b3636bb4e59' in the WHERE clause, without any luck.
  • It kinda works when I use the first column in the ORDER BY clause with the comparison operator, for example AND columnX > 'someValue', but if the ORDER BY clause contains mixture of ASC & DESC, it doesn't work.

Is there any way to solve this? I basically want to split the resultset, which will always have some kind of ORDER. I just want to load the elements after a certain ID, regardless of the sort order. Could really use some help with this.

Best Answer

Plan A. You need the entire set of columns that you are ordering by. Let's say you left off at a=4 AND b=11 AND c=2

If you are going ascending, then

WHERE (a, b, c) > (4, 11, 2)

For all DESC, change to <

Caveat: "row constructors" like that were not optimized until 5.7.

For a mixture, please provide a 2-column example without UUIDs. Provide CREATE TABLE and sample data. Preferrably ready to run in a 'fiddle'.

Plan B This is like the row_number(), which is not available before 8.0.

Create a table with a new AUTO_INCREMENT. If all the work is in a single connection, use a TEMPORARY TABLE. Then lean on that new id for "remembering where you left off".

Related Question