MySQL: selecting arbitrarily ordered rows after a specific id

MySQL

Please excuse the awkward wording of my title, I'm not quite sure how to phrase what I'm asking.

Suppose I have a table like this:

id  value
1   10
2   40
3   20
4   50
5   40

Running this query:

SELECT * FROM table ORDER BY value DESC, id DESC

Results in:

id  value
4   50
5   40
2   40
3   20
1   10

And this query:

SELECT * FROM table ORDER BY value DESC, id DESC LIMIT 2

Results in:

id  value
4   50
5   40

How do I select the next two rows? i.e. the two rows that come after id=5 when you sort by descending value without using an offset (LIMIT 2,2)

id  value
2   40
3   20

Best Answer

If you are fine about using variables and double sorting:

SELECT
  id,
  value
FROM (
  SELECT
    id,
    value,
    @check AS chk,
    @check := IF(id = 5, 1, @check)
  FROM t, (SELECT @check := 0) x
  ORDER BY
    value DESC,
    id DESC
) s
WHERE
  chk = 1
ORDER BY
  value DESC,
  id DESC
LIMIT 2
;

Here's a SQL Fiddle for it.

Related Question