I have two tables: player and player_friend. The table player has three fields: 'id', 'level' and 'username'. The table player_friend has two fields: 'player_id' and 'friend_id'. I can get all places in the ranking by querying player, sorting by 'level' and the index of the row in the result is the placing in the ranking:
SELECT (username, level) FROM player SORT BY level;
How do I query the placing in the ranking, username and level of entries in the player_friend 'friend_id' where 'player_id' = x?
For example:
id | username | level
1 | a | 1
2 | b | 3
3 | c | 2
4 | d | 5
player_id | friend_id
4 | 1
4 | 2
expected results:
placing | username | level
2 | b | 3
4 | a | 1
Explanation: friend with username "b" is the second in the global ranking and friend with username "a" is the global ranking (sorting by level), in the example it is not visible but it is only showing where player_friend 'player_id' is 4.
Thanks in advance!
I think you want row_number():
select p.*
from player_friend pf join
(select p.*, row_number() over (order by level) as placing
from player p
) p
on pf.friend_id = p.id;