Re: distinct not working in a multiple join - Mailing list pgsql-general

David Rio Deiros <driodeiros@gmail.com> writes:
> I have some issues with the query attached at the end of this email.
> If I run that query I got this output ( I have removed some of the
> fields) despite the distinct clause:

> QC Q&A  | www.xxx.com | 44281
> QC Q&A  | www.xxx.com | 44281
> WhyMAX? |             | 44285

Since you removed some fields, no one can tell if this output
is wrong or not.

>             SELECT
>                 distinct ar.title,
>                 ar.raw_data,
>                 ar.upload_dt,
>                 ar.artifact_id,
>                 g.group_id,
>                 acl.read
>             FROM

The way you formatted that makes me wonder if you think that the
DISTINCT applies only to the first column.  It does not, it applies
to all the columns together --- that is, it only removes rows that
are identical in all columns to some other row.  So if there were
some rows that were identical except for group_id, you'd get the
behavior you described.

BTW, I'm not sure I believe this is actually the same query you
ran.  The presence of the "GROUP BY ar.title" clause should have
provoked errors about ungrouped columns.  If this is an exact
copy of what you did, what Postgres version is this?

            regards, tom lane