Hi, I'm having errors with an aggregate function (min) in my order by clause. I don't understand the error message, and I've tried so many other ways to do this in HQL... I don't even think its possible using criteria, I just get all kinds of crazy errors. I'm just so frustrated. I got this far with some help on the Forums... any more help would be appreciated.

Here is my HQL:
def query1 = "select oss \
from OwnershipSnapshotSeries oss \
join oss.snapshots snap \
join snap.networks network \
group by oss \
order by min(network.networkId) "


Hibernate version: hibernate 3 that ships with grails

Mapping documents: done by grails

Code between sessionFactory.openSession() and session.close(): again, grails leaves not much to see there ..
def results = OwnershipSnapshotSeries.executeQuery(query1)

Full stack trace of any exception that occurs:
could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:615) at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412) at
(grails stuff starts here)

Name and version of the database you are using: 5.0.45-community-nt MySQL Community Edition (GPL)

The generated SQL (show_sql=true): (see below)

Debug level Hibernate log excerpt:
2009-01-30 15:22:29,402: [19360] util.JDBCExceptionReporter SQL Error: -67, SQLState: 37000
2009-01-30 15:22:29,402: [19360] util.JDBCExceptionReporter SQL Error: -67, SQLState: 37000
2009-01-30 15:22:29,402: [19360] util.JDBCExceptionReporter Not in aggregate function or group by clause: org.hsqldb.Expression@13f54ae in statement [select ownerships0_.id as id0_, ownerships0_.version as version0_, ownerships0_.country_id as country3_0_, ownerships0_.created_by_id as created4_0_, ownerships0_.date_created as date5_0_, ownerships0_.is_complete as is6_0_, ownerships0_.last_updated as last7_0_, ownerships0_.updated_by_id as updated8_0_ from ownership_snapshot_series ownerships0_ inner join ownership_snapshot snapshots1_ on ownerships0_.id=snapshots1_.series_id inner join ownership_snapshot_network networks2_ on snapshots1_.id=networks2_.ownership_snapshot_networks_id inner join network network3_ on networks2_.network_id=network3_.id group by ownerships0_.id order by min(network3_.network_id)]
2009-01-30 15:22:29,402: [19360] util.JDBCExceptionReporter Not in aggregate function or group by clause: org.hsqldb.Expression@13f54ae in statement [select ownerships0_.id as id0_, ownerships0_.version as version0_, ownerships0_.country_id as country3_0_, ownerships0_.created_by_id as created4_0_, ownerships0_.date_created as date5_0_, ownerships0_.is_complete as is6_0_, ownerships0_.last_updated as last7_0_, ownerships0_.updated_by_id as updated8_0_ from ownership_snapshot_series ownerships0_ inner join ownership_snapshot snapshots1_ on ownerships0_.id=snapshots1_.series_id inner join ownership_snapshot_network networks2_ on snapshots1_.id=networks2_.ownership_snapshot_networks_id inner join network network3_ on networks2_.network_id=network3_.id group by ownerships0_.id order by min(network3_.network_id)]


Funny thing is, the SQL that's generated above actually works in MySQL... I get exactly the results I want if I cut and paste it over into a query editor in Toad. I'm just out of ideas!!!!


(see also: http://forum.hibernate.org/viewtopic.php?t=994084 )

Thanks for any help.

I think it's because you don't have a group-function (aggregate) in your select-clause. Just try it without group-by or also select the min().

I tried it both ways... I get the same error. But the generated SQL works just fine. Makes no sense to me!

def query1 = "select oss, min(network.networkId) \
from OwnershipSnapshotSeries oss \
join oss.snapshots snap \
join snap.networks network \
group by oss \
order by min(network.networkId) "
def results = OwnershipSnapshotSeries.executeQuery(query1)

Try to group by all properties off oss, e.g. "group by oss.id, oss.version, oss.country,..."