From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Alexander Reichstadt <lxr(at)mac(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: GROUP BY or alternative means to group |
Date: | 2012-03-12 21:09:58 |
Message-ID: | CAOR=d=2sb7eZ1ThcqcbCGGkKi+shwkWxxTHy02SenReUhMwwJw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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).
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Uckun | 2012-03-12 21:11:42 | Re: full text search and ILIKE type clauses. |
Previous Message | Alexander Reichstadt | 2012-03-12 21:07:12 | Re: GROUP BY or alternative means to group |