Hi, I know this topic has been discussed to death but I see no viable solution and the only semi-work-around I see have no real explanation so I was hoping somebody would enlighten me with the answer. Thanks.

The HQL I need to run is,
"select u.name, count(*) ct from user u group by u.name order by ct desc"

After searching through this forum, now I realize HQL does not allow aliases on the selected columns. So the only possible method to achieve the desired result would be to use the "column number" as somebody proposed. ( eg] ORDER BY col#2)

Now the question is, how do I indicate the column number in HQL? Is it even possible? If not, is there a work-around for this problem?

Yes I'm using MySQL and I understand MySQL's bug is preventing me to have aggregate function in the ORDER BY. But at least MySQL has a work-around to use the aliase. Is there an equivalent in Hibernate?

I'm losing sleep over this matter... please help.
Thanks.

No, HQL does not have aliases. Why not just fall back to a native SQL query?

I thought you had to have a persistent object to fall back to native SQL.
So in this case do I need to create an object with the property (String name) and (int count) to get the result?

If this is true, then I guess I misunderstood the logic. I thought there always has to exist a database table for each persistent object.

Is this a fairly safe assumption? (I can't test out the code until tonight so I'm trying to get as much information as possible before getting at this again. Thanks.)

Oh, I mean, fall back to direct JDBC. Easy.