My dataset looks like this:
TEAM_ID PLAYER_ID NUM_POINTS
21 39 20
21 50 10
21 67 10
22 74 0
22 73 0
I want to get a subset of the data where each team has a clear "winner", that is, if I group by team ID there is exactly ONE player who has more points than all the other players. If there is a tie between two or more players on the team, I do not want them to be included in the subset. I tried out a query but the number or rows I'm getting in my subset seems way too high so I think I'm making a mistake. Here's my query:
WITH ADD_MAX_POINTS_VALUES_TO_TEAM AS (
SELECT
T1.TEAM_ID,
MAX(T1.NUM_POINTS) AS MAX_POINTS_FOR_TEAM,
FROM MY_TABLE T1
GROUP BY T1.TEAM_ID
), GET_SUBSET AS (
SELECT T1.TEAM_ID
T1.PLAYER_ID
T2.MAX_POINTS_FOR_TEAM
FROM MY_TABLE T1 INNER JOIN ADD_MAX_POINTS_VALUES_TO_TEAM T2
ON T1.B1_BUS_PRTNR_NBR = T2.B1_BUS_PRTNR_NBR
WHERE T1.NUM_POINTS = T2.MAX_POINTS_FOR_TEAM
GROUP BY 1, 2, 3
HAVING COUNT(*) = 1 -- > HERE I AM TRYING TO SAY THERE IS ONE UNIQUE PLAYER ON THE TEAM WITH THE MAX SCORE
),
SELECT COUNT(*) FROM GET_SUBSET
Any help is appreciated, let me know if I need to provide more info.
Thanks!!
We can use a subquery :
select *
from MY_TABLE a
where a.num_points > all (select op.num_points
from MY_TABLE op
where a.team_id=op.team_id
and a.player_id<>op.player_id)