I'm trying to get the max value of a count. There are two ways I can find to do it. The first is just do a count and then sort in desc order on the count(*). This will not work for me because I am using an older version of mysql (4.0) that doesn't allow for functions to be used in the order by clause. I have re-written the query (in sql) to
Code:
Select count(voteId) from vote group by videoId having count(voteId)=(Select max(A.CNT) from (Select count(videoId) as CNT from vote group by (videoId)) as A)
I'm trying to convert this to HQL but am having trouble with the max(A.CNT) part. Here is what I have so far
Code:
select count(*), v.video from Vote v where v.category.id=? and v.voteDate >= ? and v.voteDate <= ? group by v.video.id having count(*)=(select max(parent.child) from (select count(*) as child from Vote vo group by (vo.video.id)) as parent)
This query does not work. Any idea what I'm doing wrong? Is there is an alternate soultion to this?
Thanks
Tim
Login
FAQ
Search