Re: Select duplicated values

[Pete Yunker <peter.yunker@xxxxxxxxxxxxxxxx>]

Another option is to perform a self-join on columns B, C, and D (filtering out the 'same' record where a=a) instead of using the sub-select.  This may yield better performance depending on the size of the table.  Also, I don't believe the concatenation / sub-select will work if all of B, C, and D are all NULL.

SELECT x.a

FROM my_table x

INNER JOIN my_table y

ON ((x.b = y.b) or (x.b IS NULL AND y.b IS NULL))

AND ((x.c = y.c) or (x.c IS NULL AND y.c IS NULL))

AND ((x.d = y.d) or (x.d IS NULL AND y.d IS NULL))

AND x.a <> y.a

Another alternative to handling the NULL values is to COALESCE them to a value that would never exist in columns B, C, or D.  I don't know the datatypes you are using, so I'll just use 'junk' for now.

SELECT x.a

FROM my_table x

INNER JOIN my_table y

ON COALESCE(x.b,'junk') = COALESCE(y.b,'junk')

AND COALESCE(x.c,'junk') = COALESCE(y.c,'junk')

AND COALESCE(x.d,'junk') = COALESCE(y.d,'junk')

AND x.a <> y.a

---

Pete Yunker

Vice President of Data Products

Home Junction, Inc.

On Nov 21, 2011, at 5:23 PM, jeffrey wrote:

Lets say that the primary key column is A.  I am trying to select all
the rows with duplicated values in columns B, C, and D.

I am not too experienced in SQL syntax, and I've used the following:
select A from table_name where B+C+D in (select B+C+D from table_name
group by B+C+D having count(*)>1  )

I'm looking for a better way, since I am just adding the three columns
together right now.

Jeffrey

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general