Limit ordered SQL query by primary key

Question

I have a query that orders the result by a specific none-unique column. Now I want to get the first N rows after a specific ID (the primary key).

I have a table similar to this:

ID	Size
0	3
1	8
2	3
3	3
4	10
5	7

Now I order it by the Size (and ID to make the order stable) like this:

SELECT * FROM FOO
ORDER BY Size ASC, ID ASC; 

which results in:

ID	Size
0	3
2	3
3	3
5	7
1	8
4	10

now I want the first 3 elements after the row where ID == 2 so the result should be:

ID	Size
3	3
5	7
1	8

I tried:

SELECT * 
FROM foo
WHERE Size > (SELECT size FROM foo WHERE ID = 2)
ORDER BY Size ASC, ID ASC
LIMIT 3;

which results in:

ID	Size
5	7
1	8
4	10

I also tried:

SELECT * 
FROM foo
WHERE Size >= (SELECT size FROM foo WHERE ID = 2)
ORDER BY Size ASC, ID ASC
LIMIT 3;

which results in:

ID	Size
0	3
2	3
5	7

I can't figure out how to get the expected result.

Answer 1

Use ROW_NUMBER() window function:

WITH cte AS (SELECT *, ROW_NUMBER() OVER (ORDER BY Size, ID) rn FROM foo)
SELECT ID, Size
FROM cte
WHERE rn > (SELECT rn FROM cte WHERE ID = 2)
ORDER BY rn LIMIT 3;

See the demo.