How to select individual records for two columns with a join?

Question

I have a table of players and I'm trying to create an SQL query to link them in order to assign matches for a specific round. Each player has an account, and I want to combine players who have similar points. I do not want any player to appear in more than one pair.

To start, I created the following view:

CREATE VIEW all_possible_pairs AS SELECT p1.id AS player1, p2.id AS player2 FROM players as p1, players as p2 WHERE p1.id < p2.id ORDER BY ABS(p1.score - p2.score) 

all_possible_pairs receives all possible pairs of players and avoids the coincidence of players with itself and includes the same pair twice (for example, a, bb, a). The data is ordered in the way that I want the pairs that appear first are preferred for the pairs that appear later (since they have closer estimates).

I want to select lines from all_possible_pairs where the player first appears. In the resulting table, each player should appear only once after two columns (for example, if a player first appears in player2, they should not appear in player1 or player2 in any subsequent lines).

So, suppose players a, b, c, d and all_possible_players look like this:

 player1, player2 ab ac ad bc bd cd 

I want to choose from this view to get the following:

 player1, player2 ab cd 

I banged my head against the wall for several seconds, trying to use the various SELECT DISTINCT clauses, but I cannot figure out what is right. For example, the SELECT DISTINCT on player1 in the above example will contain b, c, which is not what I want, since it means that b and c are in the table twice.

Answer 1

Instead of listing all the possible pairs, create a view that gives each player a rank rather than a rating:

 CREATE VIEW ranked_players AS SELECT id AS Playerid, row_number() OVER (order by score) as PlayerRank FROM players 

Now, when you connect them, connect the odd ranking ones with neighboring neighboring neighboring neighboring neighbors:

 SELECT ranked1.PlayerId player1, ranked2.PlayerId player2 FROM ranked_players AS ranked1 INNER JOIN ranked_players AS ranked2 ON ranked1. PlayerRank+ 1 = ranked2. PlayerRank WHERE ranked1.playerrank % 2 = 1 

See if you get what you need.

Answer 2

Maybe not exactly what you want, but:

 with cte as (select *, row_number() over (order by score desc) as rn from players) select * from cte as t1 join cte as t2 on t2.rn = t1.rn+1 where mod(t1.rn, 2) = 1 and mod(t2.rn, 2) = 0 

This assigns row_number n based on the score, and then pairs n with n + 1

fiddle