title: SELECT TOP 1 in a many to many query
body: <p>I have a faily simple many to many schema, which looks like this:<a href="http://img406.imageshack.us/img406/8207/partialschema.png" rel="nofollow noreferrer">alt text http://img406.imageshack.us/img406/8207/partialschema.png</a></p>
  
  <p>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.</p>
  
  <p>What I've managed to do is:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Also, I'd rather return the separate columns from the match table as separate columns and not as a formatted lump.</p>
  
  <p><strong>Answers to requests for more info:</strong></p>
  
  <p>Yes there is an MatchDateTime column, which I intend to use for sorting.</p>
  
  <p>Yes I do want players who haven't yet played any matches, the left join is deliberate.</p>