From: Tom Lane <tgl@sss.pgh.pa.us>
To: Carlos <carlos@solaria-mediterranea.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Select distinct and order by.
Date: Tue, 17 Jul 2001 12:00:53 -0400
Message-ID: <25191.995385653@sss.pgh.pa.us> (raw)
In-Reply-To: <3B4C1145.97056A0A@solaria-mediterranea.com>
References: <3B4C1145.97056A0A@solaria-mediterranea.com>
Carlos <carlos@solaria-mediterranea.com> writes:
> Select distinct field1, field2 from table1 order by field3;
> The value return by PQresultErrorMessage is:
> For SELECT DISTINCT, ORDER BY expressions must appear in target list
> Whatever this query works fine in postgresql 6.5.3.
> Is correct this query and so there was a bug on 6.5.3 or there is a bug
> on the new versions?.
6.5 was in error to accept that query. The problem with it is: which
value of field3 should be used to sort, if multiple rows with the same
field1/field2 are being collapsed together? The results aren't
well-defined.
You can probably accomplish what you want in a slightly better-defined
way with SELECT DISTINCT ON. See the SELECT reference page.
> Also in certains situations (in versions 7.0.x) this query fails from
> libpq:
"Fails" how?
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, carlos@solaria-mediterranea.com
Subject: Re: Select distinct and order by.
In-Reply-To: <25191.995385653@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox