From: | Bartosz Dmytrak <bdmytrak(at)eranet(dot)pl> |
---|---|
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 20:57:11 |
Message-ID: | CAD8_Ucbgw+6bwvfeJDwtBOK3+pcCocwJetBr1+F81FrhhPzvug@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <lxr(at)mac(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Reichstadt | 2012-03-12 21:02:25 | Solved [Re: GROUP BY or alternative means to group] |
Previous Message | mgould | 2012-03-12 20:38:21 | Re: full text search and ILIKE type clauses. |