How To Select Row By Primary Key, One Row 'above' And One Row 'below' By Other Column?

Ok, so I've got a 'users' table with an 'id' column and a 'score' column. 'id' is the primary key, 'score' can be any positive integer, and users can have the same score. I need t

Solution 1:

First, query the score for that particular user:

select  score
from    userswhereid = 42

Say the score for user 42 is 6. You can then query the next user like:

select  name
,       score
from    users
where   score > 6orderby
        score
limit   1

and the previous user like:

select  name
,       score
from    users
where   score <6orderby
        score desc
limit   1

Solution 2:

set@ID=3;

set@Score= (select score
              from users
              where ID =@ID);

select ID, Score
from (select U.ID, U.Score
      from users as U
      where U.ID <>@IDand
            U.Score <@Scoreorderby U.Score desc limit 1) as P
unionallselect U.ID, U.Score
from users U
where U.ID =@IDunionallselect*from (select U.ID, U.Score
      from users as U
      where U.ID <>@IDand
            U.Score >@Scoreorderby U.Score limit 1) as A
orderby Score;        

Solution 3:

I suppose one problem might be that you could have more than three users of the same score, and you would not know who is "directly above" or "directly below" the target.

You might consider a subquery. The inner query would be something like

select score from users wherelast='Who'andfirst='Doctor'

For the outer query, this would get you the one directly above:

select*from users 
  where score >= (select score from users wherelast='Who'andfirst='Doctor')
  orderby score asc
  limit 1

I will leave the user directly below as an exercise to the OP.

Solution 4:

select name, score
from userswhereid >= select id 
            from userswhereid < 42 
            order score 
            limit 1
order by score
limit 3

Solution 5:

Because I need formatting to discuss Andomar's proposal:

Given some users with same score (6):

mysql>select*from sam1vp;
+------+-------+|user| score |+------+-------+|40|5||41|6||42|6||43|6||44|7|+------+-------+

A query with > / < will not return the nearest neighbors:

mysql>selectuser,score from sam1vp where score >6orderby score limit 1;
+------+-------+|user| score |+------+-------+|44|7|+------+-------+

Using >= / <= and excluding the target user will:

mysql>selectuser,score from sam1vp where score >=6anduser!=42orderby score limit 1;
+------+-------+|user| score |+------+-------+|41|6|+------+-------+