> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of John Beynon > Sent: Wednesday, October 17, 2012 6:48 AM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
