I am trying to sort using "order by" _before_ grouping with "group by" but it does not seem to work. The grouping works fine but the ordering is ignored. I am using Hibernate 3.2.6.

The hibernate query is of the form:

"from Customer where id in (select id from Customer order by id desc) group by companyName,siteName"

The part in parentheses does return results order by desc id as desired. However, once the grouping occurs, the lowest id is the one that is in the result for each group.

What I am trying to accomplish is to get a list of all entries in Customer to be grouped by company name, and only the one with the highest id to be reported in the result.

I would also prefer to avoid having to do extra work on the resulting list or collection in Java.

Any tip would be highly appreciated.
Thanks.

PS: this is feasible in MySQL, but I would like a HQL query.