Re: problem with distinct not distincting...

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of John Beynon
> Sent: Wednesday, October 17, 2012 6:48 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] problem with distinct not distincting...
>
> I have a pretty basic query;
>
> select distinct on (name) name, length(name) from drugs where
> customer_id IS NOT NULL order by name;
>
> which I'd expect to only return me a single drug name if there are
duplicates,
> yet I get
>
> name | length
> ==========
> Roaccutane | 10
> Roaccutane | 10
>
> table encoding is UTF8...
>
> I'm scratching my head!
>
> Thanks,
>
> John.
>

So, the following returns one record as expected on 9.0.3:

SELECT DISTINCT ON (f) f, length(l)
FROM (VALUES ('David','Johnston'),('David','Smith')) x (f, l)
ORDER BY f

Try:

SELECT name, count(*) FROM drugs where customer_id IS NOT NULL GROUP BY name

To see whether the GROUP BY logic considers the names identical.

Using "name" as a column name also sometimes has issues so maybe try giving
it an alias:

SELECT ... FROM (SELECT name AS customer_name FROM drugs WHERE ....) AS
drug_aliased ...

DISTINCT ON has uses but I try to avoid it myself.  In this specific case
the "ON" is redundant since a simple DISTINCT will give you the same
results.

You also need to provide the PostgreSQL version and possibly server
platform.

David J.