From: | Alexander Reichstadt <lxr(at)mac(dot)com> |
---|---|
To: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: GROUP BY or alternative means to group |
Date: | 2012-03-12 21:19:46 |
Message-ID: | 33CB72DC-68B8-4BF8-A5A9-18EE43091727@mac.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Nur Hidayat | 2012-03-12 21:28:04 | Re: A 154 GB table swelled to 527 GB on the Slony slave. How to compact it? |
Previous Message | John R Pierce | 2012-03-12 21:18:52 | Re: Upgrade questions |