Re: Select duplicated values

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general