3

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
Improve this question
2
  • Why not use an offset (especially if it solves the very problem you are asking about)?
    – Andriy M
    Sep 10, 2012 at 4:55
  • I need to be able to say "select the two rows after id=5" rather than "select two rows, starting at the third row whatever its id is".
    – hamburgerly
    Sep 10, 2012 at 5:05

3 Answers 3

Reset to default
3

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.

Improve this answer
0
2

If the id=5 is known in advance (for eaxmple, from a previous execution), and you want to use that as an offset, you can:

SELECT t.* 
FROM 
        table AS t
    JOIN
        ( SELECT value
          FROM table 
          WHERE id = 5
        ) AS o
      ON t.value = o.value AND t.id < 5 
      OR t.value < o.value 
ORDER BY 
    t.value DESC, t.id DESC 
  LIMIT 2 ;
Improve this answer
5
  • In other words, ON (t.value, t.id) < (o.value, 5) (if MySQL allowed tuple comparison).
    – Andriy M
    Sep 10, 2012 at 6:08
  • @AndriyM: In fact, MySQL allows such comparisons. I've been advised though that it may perform less adequately than the expanded version (haven't tested yet on that claim).
    – ypercubeᵀᴹ
    Sep 10, 2012 at 6:11
  • It's actually hard to tell whether tuple comparison is better than its expanded equivalent when the DBMS keeps telling you that you've got a syntax error. On a different note, I tried to have look at execution plans of both your query and mine, and, as I marvellously failed at interpreting them, I'm upvoting your suggestion as a potentially better one.
    – Andriy M
    Sep 10, 2012 at 6:25
  • The error was from the table named table: sql-fiddle-2 :)
    – ypercubeᵀᴹ
    Sep 10, 2012 at 6:28
  • Ah, what a shame, I knew about the name and fixed it when trying your original query but forgot to do that when trying the tuple comparison. Thanks!
    – Andriy M
    Sep 10, 2012 at 6:55
1

Maybe can select the top 2 records in the database where the ID is not inside the list that contain the top 2 records. (Something like the following statement but my one is in MS SQL). You can try using the limit for mysql instead of the top for MS SQL.

MS SQL

SELECT TOP 2 * FROM table WHERE id NOT IN (SELECT TOP 2 id FROM table ORDER BY value DESC, id DESC)

MYSQL

SELECT * FROM table WHERE id NOT IN (SELECT id FROM table ORDER BY value DESC, id DESC LIMIT 2) ORDER BY value DESC, id DESC LIMIT 2
Improve this answer
4
  • 1
    This is essentially a complete (and more intricate) equivalent of the standard MySQL OFFSET, which doesn't solve the OP's problem. For you are still unable to tell how many rows you need to limit the inner result set by to include rows up to the particular ID only. (You don't know beforehand whether it will be 2nd or 22nd.)
    – Andriy M
    Sep 10, 2012 at 6:01
  • @AndriyM. Yeah, you are right, by the way, where do you learn so much things about database?
    – Jack
    Sep 10, 2012 at 6:10
  • Nice but this wouldn't work in MySQL unfortunately. IN (SELECT ... LIMIT x) is not allowed.
    – ypercubeᵀᴹ
    Sep 10, 2012 at 6:16
  • I might know more about databases than you do but believe me, I still know rather little. What I do know comes mainly from experience, much less from a book on SQL or databases. (In fact, I haven't read one such book in my life, but please, please don't tell anyone!) When I don't know or forget something, I mainly consult the manuals. Participating in this community has been a great change for the better, I hope. Eventually, I might even become a more diligent learner than I have been for the last few years, who knows.
    – Andriy M
    Sep 10, 2012 at 6:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.