title: Group by grouped column?
body: <p>I have a table of node likes, which looks roughly like this:</p>
  
  <pre><code>lid nid uid type
  1   23  3   like
  2   23  1   like
  3   49  3   dislike
  4   11  6   like
  </code></pre>
  
  <p>lid = unique ID for this table, nid = "node" (content) ID, uid = user ID and type is self explanatory.</p>
  
  <p>With this query:</p>
  
  <pre><code>SELECT nid, COUNT(lid) AS score, type
  FROM node_likes
  INNER JOIN users ON node_likes.uid = users.uid
  GROUP BY nid, type
  </code></pre>
  
  <p>I can get each node with its like and dislike scores. The inner join is irrelevant; some (dis)likes are from users that no longer exist, and the join is to eliminate them.</p>
  
  <p>The result looks like this:</p>
  
  <pre><code>nid score type
  307 4     like
  307 1     dislike
  404 24    like
  </code></pre>
  
  <p>How can I then sub-group this query by <code>type</code>, and return the top-scoring node ID for each "like" type (like/dislike)?</p>
  
  <p>Ie.</p>
  
  <pre><code>nid score type
  404 24    like
  307 1     dislike
  </code></pre>