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.

It would be helpful if you included sample data for your player table. — Sentinel
– Sentinel, Commented Sep 24, 2015 at 16:33

Greg Viers · Accepted Answer · 2015-09-26 11:19:27Z

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.

You need ROW_NUMBER instead of RANK, otherwise players with the same score will get the same rank and the join returns no or multiple matching rows

dnoeth · Accepted Answer · 2015-09-24 16:33:47Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related