title: SELECT DISTINCT from multiple columns
body: <p>This is a bit of a complex query, but I have a database of snooker matches, and am trying to generate stats on who has played and won the most deciding frames (don't worry about the rules if you don't know the game).</p>
  
  <p>Table:</p>
  
  <pre><code>ID player1  player2   bestOf     player1Score      player2Score
  1   1           2          9          5                   0
  2   2           1          9          5                   4
  3   1           2          9          5                   4
  4   2           1          9          4                   5
  </code></pre>
  
  <p>What I am trying to do is something like the following:</p>
  
  <pre><code>SELECT COUNT(*) AS played, DISTINCT(player1,player2) AS playerID 
  FROM matches 
  WHERE (player1Score=BestOf / 2 + 0.5 AND player2Score=BestOf / 2 - 0.5) 
  GROUP BY playerID
  </code></pre>
  
  <p>The above query doesn't work, as I believe DISTINCT doesn't support multiple columns. The result I'm looking for from the top table is:</p>
  
  <pre><code>playerID played  won
  1           3      2
  2           3      1
  </code></pre>
  
  <p>The top row in the table is not displayed, as it is not a final frame.</p>
  
  <p>I have tried variations such as:</p>
  
  <pre><code>SELECT GROUP(player1,player2)
  SELECT player1 + player2 AS playerID, select DISTINCT(playerID)
  SELECT (player1 + player2) AS playerID GROUP BY playerID
  </code></pre>
  
  <p>and a good few others. Any hint would be appreciated!</p>