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

Answer 1

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 SELECTs, 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().)

---

Answer 2

For whatever reason the following style named query didn't work for me:

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

---

It could be because I am using an old version of Hibernate. I got the order by working by using a number to choose the column to sort by like this:

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