Hibernate version: 2

can you use hsql to "select" and "group by" without specifying all of the fields in the select and the group by? i have to retrieve 280 fields from three tables/objects (mapped many-to-one, many-to-one, many-to-one).... specifying all the fields explicitly, i get a huge sql statement which is bombing in db2...

e.g.:

select obj1.obj2.obj3.lotsoffields (...), obj1.obj2.lotsoffields (...) from Object1 obj1
group by obj1.obj2.obj3.lotsoffields (...), obj1.obj2.lotsoffields (...)

i tried something like:
select obj1.obj2.obj3, obj1.obj2 from Object1 obj1
group by obj1.obj2.obj3, obj1.obj2

...but using show_sql=true i get only the PK's in the group by, and all the fields in the select -- sql error.

thank you!

tom

No. If there's a group by clause, all pure columns returned in the result set must appear in the group by. This is an SQL restriction. Think about it like this: if you had a table containing fruit, and you wanted to group by colour, you must obviously only get a single row returned for "red": that's what group by is for. So how do you return all the names of all red fruit in the single name column? You can't. You could return the longest name, or the first name, but not all names.

Perhaps you don't want group by? In the above example, if you wanted a query to return the name of all red group, you'd use where colour = "red", not group by colour.

thanks, funny, i did not now that hql has "distinct" - all i needed was to use that key word...

tom