SQL Server : select only first instance of record with multiple columns

Question

I'm trying to get some individual stats from a score keeping system. In essence, teams are scheduled into matches

Match
---------
Matchid (uniqueidentifier)
SessionId (int)
WeekNum (int)

Those matches are broken into sets, where two particular players from a team play each other

MatchSet
-----------
SetId (int)
Matchid (uniqueidentifier)
HomePlayer (int)
AwayPlayer (int)
WinningPlayer (int)
LosingPlayer (int)
WinningPoints (int)
LosingPoints (int)
MatchEndTime (datetime)

In order to allow for player absences, players are allowed to play twice per Match. The points from each set will count for their team totals, but for the individual awards, only the first time that a player plays should be counted.

I had been trying to make use of a CTE to number the rows

;WITH cte AS
(
   SELECT *,
       ROW_NUMBER() OVER (PARTITION BY MatchId ORDER BY MatchEndTime) AS rn
   FROM 
       (SELECT 
            SetId, MS.MatchId, WinningPlayer, LosingPlayer, 
            HomePlayer, AwayPlayer, WinningPoints, LosingPoints, MatchEndTime
        FROM 
            MatchSet MS 
        INNER JOIN 
            [Match] M ON M.MatchId = MS.MatchId AND M.[Session] = @SessionId
    )

but I'm struggling as the player could be either the home player or away player in a given set (also, could either be the winner or the loser)

Ideally, this result could then be joined based on either WinningPlayer or LosingPlayer back to the players table, which would let me get a list of individual standings

it looks like `WinningPlayer` and `LosingPlayer` are redundant and can be calculated from, say `HomePoints` and `AwayPoints` instead of `WinningPoints` and `LosingPoints`. — Alex Kudryashev, Jan 26 '19 at 21:22
That's a fairly simple change to implement, but I wonder if it solves the issue. In Match #1, player 10 (home) wins against player 15, and gets 21 points. Player 15, the away player, gets 10 points. — tsweatt, Jan 26 '19 at 21:55
It isn't *the* solution but it will simplify **the** solution. — Alex Kudryashev, Jan 26 '19 at 21:59
Also look at your data schema. Currently it is possible that sets of a match can be played by different teams. Is it intentional? — Alex Kudryashev, Jan 26 '19 at 22:24

score 1 · Answer 1 · answered Jan 26 '19 at 22:06

Perhaps you could virtualize a normalized view of your data and key off of it instead of the MatchSet table.

;WITH TeamPlayerMatch AS
(
    SELECT TeamID,PlayerID=WinnningPlayer,MatchID,Points = MS.WinningPoints, IsWinner=1 FROM MatchSet MS INNER JOIN TeamPlayer T ON T.PlayerID=HomePlayer
    UNION ALL
    SELECT TeamID,PlayerID=LosingPlayer,MatchID,Points = MS.LosingPoints, IsWinner=0 FROM MatchSet MS INNER JOIN TeamPlayer T ON T.PlayerID=AwayPlayer
)

,cte AS
(
   SELECT *,
       ROW_NUMBER() OVER (PARTITION BY MatchId ORDER BY MatchEndTime) AS rn
   FROM 
       (SELECT 
            SetId, MS.MatchId, PlayerID, TeamID, Points, MatchEndTime, IsWinner
        FROM 
            TeamPlayerMatch MS 
        INNER JOIN 
            [Match] M ON M.MatchId = MS.MatchId AND M.[Session] = @SessionId
        WHERE
            IsWinner=1
    )

This works as well as Rob S' answer above, but I can only mark once answer. Thanks! — tsweatt, Jan 26 '19 at 22:23

score 1 · Accepted Answer · answered Jan 26 '19 at 22:12

I think the first step is to write a couple CTEs that get the data into a structure where you can evaluate player points regardless of win/loss. Here's a possible start:

;with PlayersPoints as 
(
   select m.MatchId
        ,m.SessionId 
        ,m.WeekNum
        ,ms.SetId       
        ,ms.WinningPlayer as PlayerId
        ,ms.WinningPoints as Points
        ,'W' as Outcome
        ,ms.MatchEndTime 
    from MatchSet ms
    join Match m on on ms.MatchId = m.MatchId
        and m.SessionId = @SessionId 

   union all 

   select m.MatchId
        ,m.SessionId 
        ,m.WeekNum
        ,ms.SetId   
        ,ms.LosingPlayer as PlayerId
        ,ms.LosingPoints as Points
        ,'L' as Outcome
        ,ms.MatchEndTime 
    from MatchSet ms
   join Match m on on ms.MatchId = m.MatchId
        and m.SessionId = @SessionId 
)
, PlayerMatch as 
(
   select SetId
        ,WeekNum
        ,MatchId
        ,PlayerId
        ,row_number() over (partition by PlayerId, WeekNum order by MatchEndTime) as PlayerMatchSequence
    from PlayerPoints 
)
....

The first CTE pulls out the points for each player, and the second CTE identifies which match it is. So for calculating individual points, you'd look for PlayerMatchSequence = 1.

This was just about exactly what I was looking for. Thanks! – tsweatt Jan 26 '19 at 22:21 — tsweatt, Jan 26 '19 at 22:21

SQL Server : select only first instance of record with multiple columns

2 Answers2