Re: GROUP BY or alternative means to group

[Kiriakos Georgiou <kg.postgresql@xxxxxxxxxxxxxx>]

Instead of the joins you can use a subquery to get the first address.

Or you can do the joins without the group by and use row_number() over(partition by companies.id) on the select list to label each company address with a number starting at 1.  You can just keep rows that have row_number = 1.  See http://www.postgresql.org/docs/current/static/tutorial-window.html to get the feeling how window functions work.

Kiriakos

On Mar 12, 2012, at 3:35 PM, Alexander Reichstadt wrote:

Hi,

the following statement worked on mysql but gives me an error on postgres:

column "addresses.address1" must appear in the GROUP BY clause or be used in an aggregate function

I guess I am doing something wrong. I read the web answers, but none of them seem to meet my needs:

SELECT companies.id,companies.name,companies.organizationkind,addresses.address1,addresses.address2,addresses.city,addresses.zip FROM companies JOIN addresses_reference ON companies.id=addresses_reference.refid_companies LEFT JOIN addresses ON addresses_reference.refid_addresses=addresses.id GROUP BY companies.id;

What I did now was create a view based on above statement but without grouping. This returns a list with non-distinct values for all companies that have more than one address, which is correct. But in some cases I only need one address and the problem is that I cannot use distinct.

I wanted to have some way to display a companies list that only gives me the first stored addresses related, and disregard any further addresses.

Is there any way to do this?

Thanks

Alex