title: Selecting sum of single column depending on two columns on same table
body: <p>I have a table very similar to the one below. <code>p1</code> and <code>p2</code> on the table refer to id of player on an another table.</p>
  
  <pre><code>id score p1 p2 date
  -- ----- -- -- ----
  1  12    1  2  2011.10.21
  2  23    3  4  2011.10.22
  3  21    1  3  2011.10.23
  4  35    5  1  2011.10.24
  5  11    2  3  2011.10.25
  </code></pre>
  
  <p>What I want to do is the get the player id (p1 or p2) with highest score. My solution is something like <code>select sum(score)</code> but I can't form a query because a player may appear in both p1 or p2 columns.</p>
  
  <p>Also a bigger problem is when I want to sort scores from highest to lowest. I dont know what to do. How can I sum and sort a score if I need to group to separate columns? The result I want is similar to this output: </p>
  
  <pre><code>pID score times_played
  --- ----- ------------
  1   68    3
  3   55    3
  5   35    1
  2   23    2
  4   23    1
  </code></pre>
  
  <p>Is my database design flawed? If there is a more intelligent way I'd like to know. Should I need seperate single queries so I can merge them on PHP or something?</p>
  
  <p>Any help would be appreciated.
  Cheers.</p>
  
  <p>PS: I couldnt think a nice subject. Feel free to edit.</p>