Search Postgresql Archives

Re: Select duplicated values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux