Tuesday, August 29, 2006

Hibernate and HSQL (group by and order by)

I was experimenting the last few days with the three big embedded Java databases Derby, McKoy, and HSQL which are supported by Hibernate. Though I had high hopes for Derby it just didn't work. McKoy was slightly better and HSQL actually worked except for the well documentes "group by" Problem:

The query : "select cat, count(*) from Cat cat group by cat" does not work.
ERROR: column "xxxx" must appear in the GROUP BY clause or be used in an aggregate function

I fixed that in my code and it still dind't work. I had something like the following:
select cat, count(*) from Cat cat group by cat oder by cat.Name

which should according to SQL Syntax work. However HSQL doesn't like it when you use "order by" with "group by". (I also tries to group y cat.Name without any success). Anyway I ended up sorting the list in memory with java's built in Collections.sort...


Anonymous Anonymous said...

i feel your pain on this one. i am running into the same problem

10:35 AM

Blogger Unknown said...

if your useing version 1.8 it will not work look here:



7:37 AM


Post a Comment

<< Home