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