How to order by count() in JPA

Question

I am using This JPA-Query: ``` SELECT DISTINCT e.label FROM Entity e GROUP BY e.label ORDER BY COUNT(e.label) DESC ``` I get no errors and the results are sorted almost correct but there are some values wrong (either two values are flipped or some single values are completly misplaced) EDIT: Adding COUNT(e.label) to my SELECT clause resolves this problem for this query. But in a similar query which also contains a WHERE clause the problem persists: ``` SELECT DISTINCT e.label, COUNT(e.label) FROM Entity e WHERE TYPE(e.cat) = :category GROUP BY e.label ORDER BY COUNT(e.label) DESC ```

Accepted Answer

You might need to include the `COUNT(e.label)` in your SELECT clause:

```
SELECT DISTINCT e.label, COUNT(e.label) 
FROM Entity e 
GROUP BY e.label 
ORDER BY COUNT(e.label) DESC
```

UPDATE: Regarding the second query please read section 8.6. Polymorphic queries of the EntityManager documentation. It seems that if you make your queries in a way that requires multiple `SELECT`s, then the `ORDER BY` won't work anymore. Using the `TYPE` keyword seems to be such a case. A quote from the above link:

The following query would return all persistent objects:

```
from java.lang.Object o // HQL only
```

The interface Named might be implemented by various persistent classes:

```
from Named n, Named m where n.name = m.name // HQL only
```

Note that these last two queries will require more than one SQL SELECT. This means that the order by clause does not correctly order the whole result set. (It also means you can't call these queries using Query.scroll().)

How to order by count() in JPA

Solution

Problem