CODEWITHSUNDEEP
sqlpostgresql
Leonardo da Silva
Leonardo da Silva

Reputation: 1495

Ranking Placing Query in POSTGRESQL

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:

player

id | username | level
 1 |        a |     1
 2 |        b |     3
 3 |        c |     2
 4 |        d |     5

player_friend

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!

Upvotes: 2

Views: 52

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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;

Upvotes: 3

Related Questions