CODEWITHSUNDEEP
javamysqljpajpql
Noah Martin
Noah Martin

Reputation: 1809

(JPQL) - Query for getting user with highest number of records

Excuse me for anking again about this issue but I need to have a JPA query for this:

select username, count(*)
from Records
group by username
order by count(*) desc
limit 1

I thought about smth like: 

select r.username,count(*) from Records r order by r.username desc

and then to call 

getResultList().get(0)

but I am allowed to write only:

select r from Records r order by r.username desc

and in this case I do not know how to get what I need. Does anyone have any idea?

Upvotes: 0

Views: 300

Answers (1)

JB Nizet
JB Nizet

Reputation: 691765

The SQL query has a group by, and orders by count. The JPA query doesn't have any group by and orders by user name. So I don't see how they could return the same thing.

The equivalent JPQL query is

select r.username, count(r.id)
from Record r
group by r.username
order by count(r.id) desc

If you call setMaxResults(1) on the Query object, the limit clause will be added to the generated SQL query, making it completely equivalent.

Upvotes: 2

Related Questions