Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6HG0ua23048 for ; Tue, 17 Jul 2001 12:00:56 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f6HG0rX25194; Tue, 17 Jul 2001 12:00:53 -0400 (EDT) To: Carlos cc: pgsql-sql@postgresql.org Subject: Re: Select distinct and order by. In-reply-to: <3B4C1145.97056A0A@solaria-mediterranea.com> References: <3B4C1145.97056A0A@solaria-mediterranea.com> Comments: In-reply-to Carlos message dated "Wed, 11 Jul 2001 10:41:41 +0200" Date: Tue, 17 Jul 2001 12:00:53 -0400 Message-ID: <25191.995385653@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200107/226 X-Sequence-Number: 4022 Carlos 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