Re: GROUP BY or alternative means to group

Thanks, I just posted my response to my own question for the archives. I take it also that group by is faster than distinct on. If it is a substantial performance gain I have to work on this some more. A subquery I would expect would be much of a drag, so for all keystroke-updated list-tables this would not be suitable I think.

Am 12.03.2012 um 21:57 schrieb Bartosz Dmytrak:

> 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.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?
>
> Thanks
> Alex
>