Getting unique record based on max conditions including null values

I need to get back one record of a player’s (Rank A players only) most recent win date (some win dates are null but we need to include them) but picking only the last place of their most recent game session. So basically in that order: get their max win_date (if null, still include them) > from there grab their max place > and from there, pick only their max game_session_id.

Table players:

Badge_No    Name     Game_Session_ID    Place    Win_Date    Rank
565         Barry    012550             4        6/17/2021    A
565         Barry    003521             2        3/04/2021    A
565         Barry    003521             3        3/04/2021    A
565         Barry    003521             4        3/04/2021    A
565         Barry    003521             5        3/04/2021    A
565         Barry    095945             1        6/17/2021    A
101         Lee      065411             1                     A
018         Jess     001561             1        5/23/2020    A
018         Jess     002075             1        5/23/2020    A         
209         Linda    026541             2        5/06/2021    A
728         Perry    000940             1        1/23/2021    B

Expected Output:

Badge_No    Name     Game_Session_ID    Place    Win_Date    Rank
565         Barry    012550             4        6/17/2021    A
101         Lee      065411             1                     A
018         Jess     002075             1        5/23/2020    A 
209         Linda    026541             2        5/06/2021    A

My (wrong) code:

select distinct badge_no, 
       name, max(game_session_id) game_session_id, 
       max(place) place, max(win_date) win_date, rank
from players p
where not exists 
    (select 'x' from players p2 
      where p2.badge_no = p.badge_no and p.rank = 'B')
group by badge_no, name, rank

Answer

Use ROW_NUMBER with an approriate partition:

WITH cte AS (
    SELECT p.*, ROW_NUMBER() OVER (PARTITION BY Badge_No
                                   ORDER BY Win_Date DESC, Place DESC, Game_Session_ID DESC) rn
    FROM players p
    WHERE "Rank" = 'A'
)

SELECT Badge_No, Name, Game_Session_ID, Place, Win_Date, "Rank"
FROM cte
WHERE rn = 1;

Advertisement

Answer