Display a ranking grid for game : optimization of left outer join and find a player
- by Jerome C.
Hello,
I want to do a ranking grid.
I have a table with different values indexed by a key:
Table SimpleValue : key varchar, value int, playerId int
I have a player which have several SimpleValue.
Table Player: id int, nickname varchar
Now imagine these records:
SimpleValue:
Key value playerId
for 1 1
int 2 1
agi 2 1
lvl 5 1
for 6 2
int 3 2
agi 1 2
lvl 4 2
Player:
id nickname
1 Bob
2 John
I want to display a rank of these players on various SimpleValue. Something like:
nickname for lvl
Bob 1 5
John 6 4
For the moment I generate an sql query based on which SimpleValue key you want to display and on which SimpleValue key you want to order players.
eg: I want to display 'lvl' and 'for' of each player and order them on the 'lvl'
The generated query is:
SELECT p.nickname as nickname, v1.value as lvl, v2.value as for
FROM Player p
LEFT OUTER JOIN SimpleValue v1 ON p.id=v1.playerId and v1.key = 'lvl'
LEFT OUTER JOIN SimpleValue v2 ON p.id=v2.playerId and v2.key = 'for'
ORDER BY v1.value
This query runs perfectly. BUT if I want to display 10 different values, it generates 10 'left outer join'. Is there a way to simplify this query ?
I've got a second question: Is there a way to display a portion of this ranking. Imagine I've 1000 players and I want to display TOP 10, I use the LIMIT keyword. Now I want to display the rank of the player Bob which is 326/1000 and I want to display 5 rank player above and below (so from 321 to 331 position). How can I achieve it ?
thanks.
