Message-ID: <3B4C1145.97056A0A@solaria-mediterranea.com>
Date: Wed, 11 Jul 2001 10:41:41 +0200
From: Carlos <carlos@solaria-mediterranea.com>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Select distinct and order by.
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


Hi all:

We have developed an aplication in C with postgresql, using libpq. Our
version of development is Postgresql 6.5.3 and it works fine.

Now we are migrating the application to various flavours of Linux
(S.u.s.e 7.1, tha uses postgresql 7.0.3; Debian Potato, that uses
postgresql 7.0.2) and in these dists the next query fails.

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?.

Also in certains situations (in versions 7.0.x) this query fails from
libpq:

Select distinct field1, field2, field3 from table1 order by field1,
field2;

but if we copy the statement with mouse and do:

  $psql -d ourdb -c "Select distinct field1, field2, field3 from table1
order by field1, field2;" WORKS FINE.

These last situation are in a transaction whith some tables locked, that
aren't table1.

TIA

Carlos.
Solaria Mediterranea, S.L.L:

P.S.: please send me the answers directly, because I isn't subscribed to
the list.