Skip site navigation (1) Skip section navigation (2)

Re: group by

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ewald Geschwinde <webmaster(at)geschwinde(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: group by
Date: 2002-01-15 20:03:52
Message-ID: 26434.1011125032@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Ewald Geschwinde <webmaster(at)geschwinde(dot)net> writes:
> I have a problem whith group by and aliases

> select s1.channelname as channelname, s1.id as channelid,
> count(*) as anz from mgw_news n left join mgw_news_channels s1 on
> n.channelid = s1.id group by channelid, channelname order by channelid,
> channelname

I would expect "group by channelid" to refer to n.channelid, which is
the SQL-mandated interpretation.  Using aliases in GROUP BY is a flat
violation of the SQL92 standard, but a lot of implementations accept it
anyway.  However, when there is an ambiguous situation, as here, we must
treat GROUP BY as referring to the underlying column name not the alias,
else we do not conform to the standard.

> this runs under mysql and postgresql seems not to accept aliases in 
> group by

> cause when i'm changing channelid to s1.id it works perfectly

> is anyone working on that?

No, because it's not a bug.  MySQL's behavior is not SQL92 compliant,
if you've described it accurately.

			regards, tom lane

In response to

  • group by at 2002-01-15 18:49:45 from Ewald Geschwinde

pgsql-novice by date

Next:From: robmayfieldDate: 2002-01-15 21:41:52
Subject: Full-text searching in PostgreaSQL - Using GiST for full-text search - examples needed
Previous:From: Josh BerkusDate: 2002-01-15 19:55:47
Subject: Re: group by

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group