How to select distinct records across two self-joined columns?

Question

I have a table of players and I'm trying to build an SQL query to pair them up to assign matches for a particular round. Each player has a score, and I want to pair players up that have the similar scores. I don't 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 gets all of the possible pairs of players and avoids matching players with themselves and including the same pair twice (e.g. as a,b b,a). The data is ordered the way I want such that pairs that appear first are preferred to pairs that appear later (since they have closer scores).

I want to select the rows from all_possible_pairs where a player first appears. In the resulting table, each players should appear only once across the two columns (e.g. if a player first appears in player2, they should not appear in player1 or player2 in any subsequent rows).

So, for example, suppose we have players a, b, c, d and all_possible_players looks like this:

player1, player2
a        b
a        c
a        d
b        c
b        d
c        d

I want to select from this view so that I get the following:

player1, player2
a        b
c        d

I've been banging my head against the wall for a while now trying various SELECT DISTINCT clauses but I can't seem to get it right. For example, a SELECT DISTINCT on player1 in the example above would include b,c, which is not what I want since it means b and c are in the table twice.

Answer 1

Instead of listing all possible pairings, create a view that gives each player a rank, rather than a score:

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

Now when you pair them, pair the odd-ranked ones with their adjacent even-ranked neighbours:

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 that gets what you need.

Answer 2

Probably 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 a row_number n based on score and then pairs n with n+1

fiddle