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