title: SQL: grouping by column after sorting
body: <p>Given a table structure with <code>name</code> and <code>rank</code> columns, with the possibility of there being duplicates for <code>name</code>, how can I get the rows unique by <code>name</code>, with maximum <code>rank</code>?</p>
  
  <p>For example, assuming the following data:</p>
  
  <pre><code>+-------+-------+
  | name  | rank  |
  +-------+-------+
  | a     | 1     |
  | a     | 2     |
  | b     | 10    |
  | b     | 20    |
  | c     | 100   |
  | c     | 200   |
  +-------+-------+
  </code></pre>
  
  <p>The query should return:</p>
  
  <pre><code>+-------+-------+
  | a     | 2     |
  | b     | 20    |
  | c     | 200   |
  +-------+-------+
  </code></pre>
  
  <p>I have the following solution that is extremely slow, which I suspect is O(N^2).</p>
  
  <pre><code>SELECT name, 
         rank 
  FROM   books temp1 
  WHERE  rank = (SELECT max(rank) 
                        FROM   book temp2 
                        WHERE  temp1.name = temp2.name) 
  </code></pre>
  
  <p>Can it be improved? Is there an altogether better way to do this?</p>
  
  <p>I'm using MySQL, and this will eventually have to be translated to JPA, so if there's a JPA/Hibernate idiom for this that would also be very appreciated.</p>