SELECT a.id as id1, b.id as id2
FROM (SELECT ROW_NUMBER() OVER (ORDER BY won DESC, id) rnum, * FROM playerstandings) a
JOIN (SELECT ROW_NUMBER() OVER (ORDER BY won DESC, id) rnum, * FROM playerstandings) b ON b.rnum = a.rnum + 1
WHERE a.rnum % 2 = 1
I think that should work, and I think that syntax is right in postgres. Not completely sure on both counts
Just to explain briefly: ROW_NUMBER() OVER gives each row a numbering based on the ordering in the brackets, I've then aliased the resulting row number to "rnum". So it joins the players by row numbers that are one apart, 1v2, 2v3, 3v4, 4v5 etc. Then the WHERE clause makes a's rnum be odd, so we end up with 1v2, 3v4, 5v6, etc.