I am using Hibernate 3.1 with MySQL and Java and I am trying to make a simple query to get the rows from table that appear more times in that table.

For example, suppose I have a table 'Sales' that stores the sales made by employees to customers. This table has two columns:

int customerId
int employeeId

Each row stores which employee sold something to which customer.
I want to retrieve the id of the employees with more sales, the number of sales of each employee, ordered by that number. In normal SQL I would do:

"select employeeId, count(employeeId) as X from Sales group by employeeId order by X"

That works perfectly in MySQL, the problem is that Hibernate (as far as I understand from the documentation) doesn't allow to usa a variable name for a column, or, in this case, use X for 'count(employeeId)'. I could change the query to:

"select employeeId, count(employeeId) from Sales group by employeeId order by count(employeeId)"

which is allowed by Hibernate, but not by MySQL, which would say: "Invalid use of group function"

I've been trying to solve it by using a SQL native query so I can use the first query I wrote, but it doesn't seem to work.

Anybody knows how to solve this?

Thanks!

SQL for MySQL
select employeeId, count(employeeId) as c from Sales group by employeeId order by c

Yes, I know that works if I write it directly in MySQL, but Hibernate won't let me use it like that.

I looked at the query generated by Hibernate and noticed that it names the columns always in the same way, for ex:

the query
select employeeId, count(employeeId) from Sales

would be traduced to something like:

select Sales.employeeId as col_0_0_, count(employeeId) as col_1_0 from Sales

So I wrote the name col_1_0 in the Hibernate query to get:

select employeeId, count(employeeId) from Sales group by employeeId order by col_1_0

It is kind of ugly, but it works.

IMO there is no need for HQL in this particular case. SQL (perhaps named query) seems more suitable.