Question 113006 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Limit ordered SQL query by primary key

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.

Tags:

3

Answers


  1. 0

    Use a WHERE clause with a subquery to only consider rows with a size greater than that of ID 3:

    SELECT * 
FROM foo
WHERE size > (SELECT size FROM foo WHERE id = 3)
ORDER BY size ASC
LIMIT 3;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    MySQL supports tuple comparison (row comparison). So that we can compare

    (a, b) > (x, y)

    which would be equivalent to

    (a > x) OR (a = x and b > y)

    Your query could be:

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

    In the past MySQl didn’t optimize row range comparisons well for an index use (not sure if they already fixed that). The workaround would be to use (a > x) OR (a = x and b > y) condition. The query would then be:

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

    To avoid code duplication we can wrap the subquery into a derived table:

    SELECT f.* 
FROM foo f
CROSS JOIN (SELECT Size, ID FROM foo WHERE ID = 2) x
WHERE ( (f.Size > x.Size) OR (f.Size = x.Size AND f.ID > x.ID) )
ORDER BY f.Size ASC, f.ID ASC
LIMIT 3;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search