ORDER BY maximum with grouped by

Question

With following table,

RECORD
---------------------
NAME            VALUE
---------------------
   Bill Clinton   100
   Bill Clinton    95
   Bill Clinton    90
Hillary Clinton    90
Hillary Clinton    95
Hillary Clinton    85
Monica Lewinsky    70
Monica Lewinsky    80
Monica Lewinsky    90

Can I, with JPA(JPQL or Criteria), select following output?

   Bill Clinton   100
Hillary Clinton    95
Monica Lewinsky    90

I mean, ORDER BY maximum VALUE group by NAME.

Answer 1

The query itself

SELECT  Name,
        MAX(value) value
FROM    record
GROUP BY Name
ORDER BY Value DESC

Output:

|            NAME | VALUE |
---------------------------
|    Bill Clinton |   100 |
| Hillary Clinton |    95 |
| Monica Lewinsky |    90 |

SQLFiddle

I'm not an expert in jpa but something between these lines might work

List<Object[]> results = entityManager
        .createQuery("SELECT  Name, MAX(value) maxvalue FROM record GROUP BY Name ORDER BY Value DESC");
        .getResultList();
for (Object[] result : results) {
    String name = (String) result[0];
    int maxValue = ((Number) result[1]).intValue();
}