CODEWITHSUNDEEP
sqlgroup-byteradatahaving
ellen
ellen

Reputation: 704

Check how many rows in a grouping have the MAX value SQL

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!!

Upvotes: 3

Views: 80

Answers (3)

dnoeth
dnoeth

Reputation: 60482

This returns rows with unique best players:

SELECT *
FROM MY_TABLE
QUALIFY 
   RANK() OVER (PARTITION BY TEAM_ID 
                ORDER BY NUM_POINTS DESC WITH TIES HIGH) = 1

The WITH TIES HIGH option is a Teradata extension implementing modified competition ranking.

Standard ranking (RANK [WITH TIES LOW]) assigns two gold medals when two best players exist, while this assigns two silver medals -> If there's a rank 1 the best points are unique.

Upvotes: 3

tinazmu
tinazmu

Reputation: 5139

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)

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522506

We can use the RANK() window function here:

WITH cte1 AS (
    SELECT t.*, RANK() OVER (PARTITION BY TEAM_ID ORDER BY NUM_POINTS DESC) rnk
    FROM MY_TABLE t
),
cte2 AS (
    SELECT TEAM_ID
    FROM cte1 t1
    WHERE rnk = 1 AND
          NOT EXISTS (
              SELECT 1
              FROM cte1 t2
              WHERE t2.TEAM_ID = t1.TEAM_ID AND
                    t2.PLAYER_ID <> t1.PLAYER_ID AND
                    t2.rnk = 1
          )
)

SELECT t1.TEAM_ID, t1.PLAYER_ID, t1.NUM_POINTS
FROM MY_TABLE t1
INNER JOIN cte2 t2
    ON t2.TEAM_ID = t1.TEAM_ID;

Demo

The first CTE assigns a rank per team based on the number of points. The second CTE then identifies teams having only a single rank 1 record.

Upvotes: 2

Related Questions