Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Dear all,

Hibernate version: 3.0.5

Name and version of the database you are using: Mysql 4.1

I'd like to issue the below HQL with the result order by the second column,

select ds.dataSourceId as id , count(distinct doc.author) as authorcount
from TbDataSource ds left join ds.tbDocuments doc group by ds order by authorcount desc

However, the HQL is convert to the below SQL:

select tbdatasour0_.data_source_id as col_0_0_, count(distinct tbdocument1_.author) as col_1_0_ from tb_data_source tbdatasour0_ left outer join tb_document tbdocument1_ on tbdatasour0_.data_source_id=tbdocument1_.data_source_id group by tbdatasour0_.data_source_id order by authorcount desc

The problem is the alias "authorcount" I defined is changed to "col_1_0_" by Hibernate which cause "Unknown column 'authorcount' in 'order clause'" error. How can I solve this problem? Thanks.

I know in most databases you cannot do that in SQL. You cannot use the alias in the order by clause. Have you tried this?

no ... mysql do not allow group function in order by cause.