Re: GROUP BY or alternative means to group

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 <lxr(at)mac(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general