I just managed to solve the problem infact.
The trailing 'e' character on the name was different for one row. All
my tools, (pgadmin and the source data in openoffice) showed the same
'e' character but psql showed it as different character...
Thanks for all taking the time to read / answer. It stumped me for a while!
John.
On Wed, Oct 17, 2012 at 4:01 PM, David Johnston <polobo@yahoo.com> wrote:
>> -----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.
>
>
--
John Beynon
Kyanmedia Ltd.
Direct line: 01483 405210
http://kyan.com
Registered in England. Company number: 4575679
Registered Office: 171 High Street, Guildford GU1 3AJ
