CODEWITHSUNDEEP
mysqlsql
sam1vp
sam1vp

Reputation: 78

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 to select 3 rows:

Any suggestions on how to do this in SQL? Thanks!

UPDATE

Sorry all, I realize now that I need to make more choices about how to handle multiple rows with the same score. I've decided that tying scores will be excluded, so I'm looking for a user with the next highest score and next lowest score from the target user.

Sample data: 

id  score  
1   0  
2   5  
3   9  
4   5  
5   5  *
6   5  
7   8  *  
8   3  *

So, if my target user has id = 5, I want rows with ids 7, 5, and 8

Upvotes: 2

Views: 4423

Answers (5)

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38745

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> select user,score from sam1vp where score > 6 order by score limit 1;
+------+-------+
| user | score |
+------+-------+
|   44 |     7 |
+------+-------+

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

mysql> select user,score from sam1vp where score >= 6 and user != 42 order by score limit 1;
+------+-------+
| user | score |
+------+-------+
|   41 |     6 |
+------+-------+

Upvotes: 0

TreeBucket
TreeBucket

Reputation: 143

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

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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 <> @ID and
            U.Score < @Score
      order by U.Score desc limit 1) as P
union all
select U.ID, U.Score
from users U
where U.ID = @ID
union all
select *
from (select U.ID, U.Score
      from users as U
      where U.ID <> @ID and
            U.Score > @Score
      order by U.Score limit 1) as A
order by Score;

Upvotes: 1

rajah9
rajah9

Reputation: 12329

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 where last='Who' and first='Doctor'

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

select * from users 
  where score >= (select score from users where last='Who' and first='Doctor')
  order by score asc
  limit 1

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

Upvotes: 0

Andomar
Andomar

Reputation: 238078

First, query the score for that particular user:

select  score
from    users
where   id = 42

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

select  name
,       score
from    users
where   score > 6
order by
        score
limit   1

and the previous user like:

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

Upvotes: 2

Related Questions