Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
Why not use an offset (especially if it solves the very problem you are asking about)? –  Andriy M Sep 10 '12 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 '12 at 5:05
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
That makes my brain hurt but it works! Thanks, dude. –  hamburgerly Sep 10 '12 at 5:34
    
@hamburgerly: Happy to be of help. Please consider having a look at ypercube's suggestion too. I'm not sure if it's better or not, but you could try both in your environment, then, perhaps, tell us if there was a significant difference (and possibly change your decision about the accepted answer). –  Andriy M Sep 10 '12 at 6:58
add comment

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
share|improve this answer
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 '12 at 6:01
    
@AndriyM. Yeah, you are right, by the way, where do you learn so much things about database? –  Jack Sep 10 '12 at 6:10
    
Nice but this wouldn't work in MySQL unfortunately. IN (SELECT ... LIMIT x) is not allowed. –  ypercube Sep 10 '12 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 '12 at 6:52
add comment

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 ;
share|improve this answer
    
In other words, ON (t.value, t.id) < (o.value, 5) (if MySQL allowed tuple comparison). –  Andriy M Sep 10 '12 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 '12 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 '12 at 6:25
    
The error was from the table named table: sql-fiddle-2 :) –  ypercube Sep 10 '12 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 '12 at 6:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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