Find players who have all the "win", "win" and "lose" results
player1_id | score1 | score2 | player2_id
-----------+--------+--------+-----------
1 | 1 | 1 | 2
3 | 1 | 1 | 1
11 | 1 | 0 | 20
5 | 1 | 1 | 55
200 | 1 | 2 | 11
17 | 1 | 1 | 7
11 | 1 | 3 | 4
11 | 1 | 1 | 100
20 | 1 | 1 | 2
20 | 2 | 1 | 33
A player has win, win, or lose results for 1 and 2. I need to find players who have all win, win and lose results. In this case, players 11 and 20.
I'm stuck here, any help was much appreciated.
+3
source to share
2 answers
If I understand correctly you need this:
select p from (
select player1_id as p, case when score1>score2 then 'W' when score1=score2 then 'D' when score1<score2 then 'L' end as res from your_table
union all
select player2_id as p, case when score1>score2 then 'L' when score1=score2 then 'D' when score1<score2 then 'W' end as res from your_table
) t
group by p
having count( distinct res ) = 3
+2
source to share
You need to get all players in the same column along with the ratings or indicator of the groups you want:
select p1
from ((select player1_id as p1, player2_id as p2, score1 as s1, score2 as s2
from t
) union all
(select player2_id as p1, player1_id as p2, score2 as s1, score1 as s2
from t
)
) t
group by p1
having sum( (s1 > s2)::int) > 0 and
sum( (s1 = s2)::int) > 0 and
sum( (s1 < s2)::int) > 0;
+2
source to share