SELECT TOP 1 in a many to many query

Question

I have a faily simple many to many schema, which looks like this:

What I'm trying to do is select all players with some arbitary conditions and I also want to select their most recent match, if they've played in one.

What I've managed to do is:

SELECT tblPlayer.PlayerId, tblPlayer.Surname, tblPlayer.Forename,
(SELECT TOP 1 tblMatch.HomeClub + ' v ' + tblMatch.OpponentClub + ' ' + tblMatch.AgeGroup + ' (' + CONVERT(VARCHAR, tblMatch.MatchDateTime, 103) + ')'
 FROM tblAppearance
 INNER JOIN tblMatch ON tblAppearance.MatchID = tblMatch.MatchID
 WHERE tblAppearance.PlayerID = tblPlayer.PlayerID 
 ORDER BY MatchDateTime DESC) AS Match
FROM tblPlayer
LEFT JOIN tblAppearance ON tblAppearance.PlayerId = tblPlayer.PlayerId
LEFT JOIN tblMatch ON tblMatch.MatchId = tblAppearance.MatchId
WHERE tblPlayer.Forename LIKE '%rob%' AND tblPlayer.Surname LIKE '%white%'
ORDER BY tblPlayer.Surname, tblPlayer.Forename, tblPlayer.DOB, tblMatch.MatchDateTime DESC

The problem is that this selects all the matches that a player has been in, not just their most recent one. I know this should be simple, but I can't seem to get the right syntax for it.

Also, I'd rather return the separate columns from the match table as separate columns and not as a formatted lump.

Answers to requests for more info:

Yes there is an MatchDateTime column, which I intend to use for sorting.

Yes I do want players who haven't yet played any matches, the left join is deliberate.

Answer 1

If i understand correctly you can try this

DECLARE @User TABLE(
     UserID INT
)

DECLARE @Matches TABLE(
     MatchID INT
)

DECLARE @UserMatches TABLE(
     UserMatchID INT,
     UserID INT,
     MatchID INT
)

INSERT INTO @User SELECt 1
INSERT INTO @User SELECt 2

INSERT INTO @Matches SELECt 1
INSERT INTO @Matches SELECt 2

INSERT INTO @UserMatches SELECt 1, 1, 1
INSERT INTO @UserMatches SELECt 2, 1, 2
INSERT INTO @UserMatches SELECt 3, 2, 2

SELECT  u.*,
     m.*
FROm    @UserMatches um INNER JOIN
     (
      SELECT UserID,
        MAX(UserMatchID) MaxID
      FROM @UserMatches um
      GROUP BY UserID
     ) MaxIdsPerUser ON um.UserID = MaxIdsPerUser.UserID
         AND um.UserMatchID = MaxID INNER JOIN 
     @User u ON um.UserID = u.UserID INNER JOIN
     @Matches m ON um.MatchID = m.MatchID

It is possible that if you have a DateTime to determine the most recent match, you can use that for the max.

Answer 2

If the MatchID is an incremental number, you could base the most recent match for each player being the MAX number

Answer 3

Instead of using tblAppearance in join, use a derived table that selects just the last appearance of the player, using APPLY, and join on this derived table:

SELECT ...
FROM tblPlayer
OUTER APPLY (
  SELECT TOP (1) *
  FROM tblAppearance
  WHERE tblPlayer.PlayerId = tblAppearance.PlayerId
  ORDER BY MatchDateTime DESC) AS lastAppearance
LEFT JOIN tblMatch ON tblMatch.MatchId = lastAppearance.MatchId
WHERE ...

Answer 4

For these kinds of problems (get top row in a group) I've had the most success, in both performance and maintainability, using ROW_NUMBER() and a CTE. The pattern is simple: the CTE selects the columns you want, and adds an additional column for the ROW_NUMBER() within each group (ordered by your desired order, of course). Then the post-CTE part of the query restricts results to those where the ROW_NUMBER() is 1.

Like this:

WITH cte AS
(
    SELECT tblPlayer.PlayerId, Surname, Forename, HomeClub, OpponentClub, AgeGroup, MatchDateTime, DOB,
     ROW_NUMBER () OVER (PARTITION BY tblPlayer.PlayerId ORDER BY MatchDateTime DESC) AS RowNum
    FROM tblPlayer
     LEFT JOIN tblAppearance ON tblAppearance.PlayerId = tblPlayer.PlayerId
     LEFT JOIN tblMatch ON tblMatch.MatchId = tblAppearance.MatchId
    WHERE Forename LIKE '%rob%' AND Surname LIKE '%white%'
)
SELECT PlayerId, Surname, Forename, HomeClub, OpponentClub, AgeGroup, MatchDateTime, DOB
FROM cte
WHERE RowNum = 1
ORDER BY Surname, Forename, DOB, MatchDateTime

Note that I'm not assuming that any IDs are ordered in the same way that the MatchDateTime's are-- there are lots of reasons (e.g. advance scheduling) why that assumption may not hold. If, however, appearance IDs are ordered identically to dates, then the query above can be made much more efficient because you won't have to do any joins to find the MatchID's you're looking for.

Note that if you have a very large number of players (100,000+) and you run this query often, there are optimizations you'll want to do here, since every time you run this query you'll do a table scan of the Player table to support your LIKE filter. If this is the case, you'll probably want to creat a covering index on Surname, Forename and get SQL to run your query in stages: first filter Player records using the covering index, then do your join, and finally pull out other Player columns from the clustered index. It's usually hard to get SQL to perform plans like this (you may need a temp table for the intermediate results), but for very large tables the perf win is worth it. If you have a small number of players, ignore the preceding paragraph. :-)