problems with Hibernate request

[Olexiy Prokhorenko]

I have problem with the following request.
In SQL I can use the following query:

select r.recId, r.recName, count(sr.subrecId) x from RECORDS r left
 join SUBRECORDS sr on r.recId=sr.subrecRecId group by r.recId order by x;

And this works perfectly well -- giving me all what I need and correct amount of sub. records for each record, and it perfectly sort everything by amount of sub.records.

I really cannot create same request with Hibernate. All the time it give me an error.
This is how I am doing that

select r from Record r join r.subRecords sr group by r order by count(sr)

Always give me an error - Invalid use of group function.

I am not sure what I am doing wrong, because in Hibernate's
manual there is an example which looks like my query. But they pretend it is working...


Where could be my problem?

Thanks!
[ June 28, 2005: Message edited by: Olexiy Prokhorenko ]

[Olexiy Prokhorenko]

I guess it's MySQL who is guilty in this problem :-(

[Juanjo Bazan]

Originally posted by Olexiy Prokhorenko:

select r from Record r join r.subRecords sr group by r order by count(sr)

You are grouping by a table. Try grouping by a column, something like: "group by r.recId"

HTH