aspose file tools*
The moose likes JDBC and the fly likes Not in aggregate function or group by clause - HSQLDB doesn't like the ORDER BY clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Not in aggregate function or group by clause - HSQLDB doesn Watch "Not in aggregate function or group by clause - HSQLDB doesn New topic
Author

Not in aggregate function or group by clause - HSQLDB doesn't like the ORDER BY clause

Stephane Eybert
Ranch Hand

Joined: Mar 15, 2010
Posts: 34
I have an Hibernate application with the mapping:

<property name="firstname" type="string">
<column name="firstname" not-null="true" />
</property>
<property name="lastname" type="string">
<column name="lastname" not-null="true" />
</property>

I get an exception in HSQLDB only and not in MySQL.

Here is the exception:

Caused by: java.sql.SQLException: Not in aggregate function or group by clause: org.hsqldb.Expression@4c8167 in statement [select count(*) as y0_ from admin this_ order by this_.firstname asc, this_.lastname asc]

Here is the sql statement:

select count(*) as y0_ from admin this_ order by this_.firstname asc, this_.lastname asc

Any clue ?
Stephane Eybert
Ranch Hand

Joined: Mar 15, 2010
Posts: 34
Some forum post says HSQLDB requires a String in the ORDER BY clause.

Fine. Mine is a String.

So what's wrong then ?
Mary Chellapa
Ranch Hand

Joined: Jul 26, 2011
Posts: 93
you are using count(*) ... i wonder why are you using it with order by ??? i think you want a group by there


Mary
Stephane Eybert
Ranch Hand

Joined: Mar 15, 2010
Posts: 34
Hello Mary,

Thanks for that comment. It's a good question.

But I don't have the answer, it's Hibernate that generates this statement.

I'm having a pagination method and I need to have a count on the number of rows. I guess that is where the statement is coming from.



And this pagination method is called by my dao method.



Thanks again !

Stephane
Daniel Doboseru
Ranch Hand

Joined: Sep 26, 2011
Posts: 57
To solve this SQL error, include in the GROUP BY clause, all the columns (at least, this is how I solved it), in order to tell the parser how to order your records.

For instance if you have the table: Student (name, age, university ....) you should have an ORDER BY like this: "ORDER BY name, university, age" or at least "name, university", so he can order by name, then by university, then by age etc. Try this!
Stephane Eybert
Ranch Hand

Joined: Mar 15, 2010
Posts: 34
Hi Daniel,

Thanks for that but I don't see any GROUP BY statement in my source code. Do you see any ?

Stephane
Stephane Eybert
Ranch Hand

Joined: Mar 15, 2010
Posts: 34
Here is more on the error message:

Caused by: java.sql.SQLException: Not in aggregate function or group by clause: org.hsqldb.Expression@1504a84 in statement [select count(*) as y0_ from admin this_ where (lower(this_.firstname) like ? or lower(this_.lastname) like ? or lower(this_.login) like ? or lower(this_.email) like ?) order by this_.firstname asc, this_.lastname asc]
Stephane Eybert
Ranch Hand

Joined: Mar 15, 2010
Posts: 34
I don't know what happened, but the issue does not show up anymore.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19784
    
  20

I hate it when that happens. You don't know what solved the issue, so it may come back at some time. I'd rather have the error occur a little longer until I find the solution, because then I know for sure the problem won't reoccur. There's not much I hate more than sleeping bugs.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Stephane Eybert
Ranch Hand

Joined: Mar 15, 2010
Posts: 34
Hi Rob,

I know, I feel the same, that's why I'm right now still looking at it, trying to dig around..

Stephane
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Not in aggregate function or group by clause - HSQLDB doesn't like the ORDER BY clause