[HQL/PostGreSql] Aggregation GROUP BY or DISTINCT

muaddib · **Joined:** Tue Sep 27, 2005 4:07 pm **Posts:** 7

Hi,

Since a transfer my databse from MySql to PostGreSql, i have a bug when executing this query in HQL :

Code:

from table1  table WHERE table.IdInstitution=2 GROUP BY table.institutionAddress

i have this exception :

Quote:

Caused by: org.postgresql.util.PSQLException: ERROR: column "table0_.lang1" must appear in the GROUP BY clause or be used in an aggregate function

I have looked for solutions on web and GROUP BY doesn't seem to work with PostGreSql ...

Ok, but i want to group my institution by addresses. In fact, i have several institutions and each institution can have multiple addresses. And i just want to show each institution with different adress :
idInstitution |Address | city
2 |address1| Paris
2 |address2|New York
3 |address3|Los Angeles

I think solution must be found by using DISTINCT.

But with the previous code, i was able to work with objects table, and i want to. How to di this by using DISTINCT ??

Thank you for your help and sorry for my bad english ...

kgignatyev · **Posted:** Mon Mar 20, 2006 2:43 pm

Postgres is very good at following SQL standards, and the use of GROUP BY is illegal because there are no aggregation function was specified in the query (like count, sum, etc. ).

select distinct ( table.address, table.city) from table1 table WHERE table.IdInstitution=2
should do that you need.

Note 1: you will get back list of arrays.
Note 2: that select distinct from EntityName does not make sense from ORM perspective because since there are distinct record for those objects then ORM considers them different objects.

muaddib · **Joined:** Tue Sep 27, 2005 4:07 pm **Posts:** 7

Thanks for your reply. But i have some others questions :

i'd like te get objects "table" with my query.

So, i used your solution and modify it like that :

Code:

select distinct on (table.InstitutionAdress1) FROM table1 table

And i have an error :
[quote]javax.servlet.ServletException: undefined alias: on [select distinct on (table.InstitutionAdress1) FROM com.database.hibernate.table1 table[/code]

I just wanted that the query send me all table1 objects that have differents institutionAddress ...

isn't it possible ?

If not, if i use your solution, when a get the list of array, how to get informaitons bout it ?
Is it [Object;Object;Object] and i have to cast with String or Integer, etc ... ?

Thanks for reply !

kgignatyev · **Posted:** Tue Mar 21, 2006 11:40 am

No need for 'on'
http://www.postgresql.org/docs/8.0/stat ... S-DISTINCT

and the query should be SQL query not HQL