Message-ID: <3E7091DF.AECA1635@rodos.fzk.de>
Date: Thu, 13 Mar 2003 15:12:47 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: franco@akyasociados.com.ar
Subject: Re: What's wrong with this group by clause?
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

> Hi everyone.=20
>
> Below you can find a simplified example of a real case.=20
> I don't understand why I'm getting the "john" record twice.=20
> I know there's no point in using constants in the group by, but makes
sense=
>  in=20
> the real much bigger query.=20
> What I see is that when I issue an EXPLAIN with this query, it tells
me tha=
> t=20
> the SORT KEY is "0".=20
> When I remove one of the constant fields and try again the EXPLAIN, it
says=
> =20
> that the SORT KEY is "0, name" (and the group by works, it shows only
one=
> =20
> record for each person in the table).
>
> Thanks anyone.
>
> /*EXAMPLE*/
> CREATE TABLE people
> (
>    name TEXT
> );
> INSERT INTO people VALUES ('john');
> INSERT INTO people VALUES ('john');
> INSERT INTO people VALUES ('pete');
> INSERT INTO people VALUES ('pete');
> INSERT INTO people VALUES ('ernest');
> INSERT INTO people VALUES ('john');
> =20=20=20
> SELECT
>    0 AS field1,
>    0 AS field2,=20
>    name
> FROM
>    people
> GROUP BY
>    field1,
>    field2,
>    name;
>
I did your example and did not get the "john" record twice.
Instead I got what you expected.
 field1 | field2 |  name
--------+--------+--------
      0 |      0 | ernest
      0 |      0 | john
      0 |      0 | pete
(3 rows)
What version are you using?

Regards, Christoph