Re: Select distinct and order by.

Carlos <carlos(at)solaria-mediterranea(dot)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