I guess I lack the knowledge to integrate your answer in my query....Actually I'd prefer to always see the first address entered unless there is a where-clause added. Not sure how this works out then and haven't tested. But given the initial query extended by distinct on it would be like so:
>> SELECT distinct on (companies.id) >> 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 where addresses.city ILIKE '%bla%' I guess postgres would make sense and deliver the entry with bla with the where-clause, and disregard the bla entry returning random addresses associated with the company without the where-clause. But where would I insert the max(address) piece? Am 12.03.2012 um 22:09 schrieb Scott Marlowe: > On Mon, Mar 12, 2012 at 1:35 PM, Alexander Reichstadt <l...@mac.com> 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? > > If you don't care which address you get, you can use max(address) or > min(address). > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
