sql and getting elements around a specific one

Question

My sql database stores the highscores for the players of a game like this:

playerName
scores

To show the scores I'm trying to create a query that returns for a playerId "joe" the positon of joe as well as one player with more and less points than joe:

(rank) (playerName) (scores)
5000    luci       2001
5001    joe        1900
5002    marc       1750

(I added the rank here just to make it more clear. It isn't stored and represents just the position in the list when sorting by the scores) Is there a way to do soemthing like this with a query without getting half of the database as result?

the `rank` column seems pretty convenient... can you just select where rank between joe's rank + or - 1 ? — Fosco, Jun 09 '11 at 20:25
@FOSC: The rank column dosn't exist at the moment as it would be redundant and I would have to update all rank rows if one user submits higher scores. But at the moment I'm pondering if it wouldn't be better to add such a row as the other path with multiple select/joins seems to be rather complex to me. — Michael, Jun 09 '11 at 22:19

score 0 · Answer 1 · answered Jun 09 '11 at 20:25

0

To determine joe's rank you have to query the db and order by scores.

answered Jun 09 '11 at 20:25

Scott C Wilson

19,102
10
61
83

Yes, order by and limit is an option but with my knowledge i would get in this case 5002 entries or all of them and I wonder if there isn't a way with better performance / less data to receive from the database. – Michael Jun 09 '11 at 20:31

score 0 · Accepted Answer · edited May 23 '17 at 12:04

0

You'd ideally want to be using window functions to do this, namely row_number(), lead() and lag().

These are not available in MySQL, but here are a few workarounds (also row_number(), picked up from the same author).

Using window functions:

with
players as (
select row_number() over w as rank,
       playername,
       scores
from players
window w (order by scores desc)
),
player as (
select rank
from players
where playername = 'joe'
)
select rank,
       playername,
       scores
from players
join player on players.rank between player.rank - 1 and player.rank + 1

Alternatively (slightly faster):

with
player_range as (
select scores - 100 as scores -- or whatever is large enough based on your stats
from players
where playername = 'joe'
),
players as (
select row_number() over w as rank,
       players.playername,
       players.scores
from players
join player_range
on player_range.scores <= players.scores
window w (order by scores desc)
),
player as (
select rank
from players
where playername = 'joe'
)
select rank,
       playername,
       scores
from players
join player on players.rank between player.rank - 1 and player.rank + 1

edited May 23 '17 at 12:04

Community

1
1

answered Jun 09 '11 at 20:27

Denis de Bernardy

75,850
13
131
154

Hmm, I tried to experiment with this a bit but they seem to be rather complex to me. Also they could eat up alot of performance, not sure about this. But thanks for the link. – Michael Jun 09 '11 at 23:06
If you need a rank/lead/lag from the DB, there's no escaping it. And yes, performance will suck short of using a [proper DB engine](http://www.postgresql.org/docs/9.0/static/index.html). – Denis de Bernardy Jun 09 '11 at 23:07
Ok, I will switch to Postgre this is probably the best solution. Just checked and my web host supports this. – Michael Jun 09 '11 at 23:38
I've updated the answer so you can see how it's done in postgres using with statements and window functions. – Denis de Bernardy Jun 09 '11 at 23:44

sql and getting elements around a specific one

2 Answers2