CODEWITHSUNDEEP
mysqlsqlhibernatejpa
George Armhold
George Armhold

Reputation: 31074

SQL: grouping by column after sorting

Given a table structure with name and rank columns, with the possibility of there being duplicates for name, how can I get the rows unique by name, with maximum rank?

For example, assuming the following data:

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

The query should return:

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

I have the following solution that is extremely slow, which I suspect is 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 an altogether better way to do this?

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.

Upvotes: 4

Views: 1784

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107786

Normally all you would need is a standard group by

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

But since it is MySQL, there is also an alternative

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

Upvotes: 1

spencer7593
spencer7593

Reputation: 108490

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

That's the simplest query to return the result set in your example.

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

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

Upvotes: 5

Related Questions