Re: [GENERAL] GROUP BY or alternative means to group

On Mon, Apr 09, 2012 at 13:55:04 -0400, Michael Gould wrote: Thanks that is a help. I would be nice if any key could be used as those are normally the things I would do group by's This is what the 9.1 documentation says: "When GROUP BY is present, it is not valid for the SELECT list express

Re: [GENERAL] GROUP BY or alternative means to group

Thanks that is a help. I would be nice if any key could be used as those are normally the things I would do group by's Regards Mike Gould From my Samsung Android tablet on T-Mobile. The first nationwide 4G networkBruno Wolff III wrote:On Mon, Mar 12, 2012 at 16:18:05 -0400,    Michael Gou

Re: [GENERAL] GROUP BY or alternative means to group

On Mon, Mar 12, 2012 at 16:18:05 -0400, Michael Gould wrote: You need to include all columns that are not aggregrative columns in the group by. Even though that is the standard it is a pain to list all columns even if you don't need them In later versions of postgres this is relaxed a bit

Re: [GENERAL] GROUP BY or alternative means to group

You need to include all columns that are not aggregrative columns in the group by. Even though that is the standard it is a pain to list all columns even if you don't need them Best Regards Michael Gould Sent from Samsung mobile Alexander Reichstadt wrote: >Hi, > >the following statement w

Re: [GENERAL] GROUP BY or alternative means to group

On Mon, Mar 12, 2012 at 3:19 PM, Alexander Reichstadt wrote: > But where would I insert the max(address) piece? > Just put max() or min() around any field in the select list that's not in the group by clause -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes t

Re: [GENERAL] GROUP BY or alternative means to group

I guess I lack the knowledge to integrate your answer in my queryActually 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: >

Re: [GENERAL] GROUP BY or alternative means to group

On Mon, Mar 12, 2012 at 1:35 PM, Alexander Reichstadt 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

Re: [GENERAL] 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 th

Re: [GENERAL] GROUP BY or alternative means to group

Instead of the joins you can use a subquery to get the first address. Or you can do the joins without the group by and use row_number() over(partition by companies.id) on the select list to label each company address with a number starting at 1. You can just keep rows that have row_number = 1.

Solved [Re: [GENERAL] GROUP BY or alternative means to group]

So the mysql way for group by seems to be non-standard. What works for postgres is the DISTINCT ON (fieldname) approach. Thanks Am 12.03.2012 um 20:35 schrieb Alexander Reichstadt: > Hi, > > the following statement worked on mysql but gives me an error on postgres: > > column "addresses.add

Re: [GENERAL] GROUP BY or alternative means to group

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 boolea