SQL: grouping by column after sorting

Question

Given the structure of the table with columns nameand rank, with the possibility of having duplicates for name, how can I get rows unique by name, with a maximum value rank?

For example, assuming the following data:

+-------+-------+
| name  | rank  |
+-------+-------+
| a     | 1     |
| a     | 2     |
| b     | 10    |
| b     | 20    |
| c     | 100   |
| c     | 200   |
+-------+-------+

The request should be returned:

+-------+-------+
| a     | 2     |
| b     | 20    |
| c     | 200   |
+-------+-------+

I have the following solution, which is extremely slow, and I suspect O (N ^ 2).

SELECT name, 
       rank 
FROM   books temp1 
WHERE  rank = (SELECT max(rank) 
                      FROM   book temp2 
                      WHERE  temp1.name = temp2.name) 

Can it be improved? Is there a better way to do this?

I use MySQL, and this should ultimately be translated into JPA, so if there will be a JPA / Hibernate idiom, which will also be very appreciated.

Answer 1

select name, max(rank) as MaxRank
    from books
    group by name

Answer 2

SELECT name 
     , MAX(rank) AS rank
  FROM books
 GROUP BY name

.

Answer 3

SELECT name, 
       max(rank) as rank
FROM   books temp1
GROUP BY name

MySQL,

SELECT name, 
       rank
FROM   (
    SELECT name, rank
    FROM books
    ORDER BY name, rank desc) ordered
GROUP BY name