Hi, You can use one of windowing function: http://www.postgresql.org/docs/9.1/static/tutorial-window.html http://www.postgresql.org/docs/9.1/static/functions-window.html this could be rank() in subquery or first_value(vale any), but there could be performance issue
another solution could be boolean flag "default" in table address_reference which should be unique for single company, I mean value true should be unique - this could be reached by unique partial index on column* *refid_companies with condition default = true http://www.postgresql.org/docs/9.1/static/indexes-partial.html#INDEXES-PARTIAL-EX3 hope Your pg version supports windowing functions (as I remember 8.4 and above) Of course there is a solution with subquery which finds min id in table addresses of each refid_companies in table addresses_reference and this subquery is joined with companies table, but I am afraid this is not the best one. Regards, Bartek 2012/3/12 Alexander Reichstadt <l...@mac.com> > 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.idGROUP > 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 >
