Thread: Select distinct and order by.

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.

On Wed, 11 Jul 2001, Carlos wrote:

> 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

That's because this query is ambiguous.

What if table1 looks like this:

field1    field2    field3
------    ------    ------
a    b    1
a    b    3
c    d    2

What should your query return then?  Both

a    b
c    d

and

c    d
a    b

are valid, depending on which 'a b' row was chosen.

If 6.5.3 allowed that, it was a mistake to rely on it because the answer
is undefined.
-- 
Tod McQuillin

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