Thread: Finding duplicated values

I was trying to use the code below but it returned no rows.

On Thu, 21 Oct 2004, Kent Anderson wrote:

> I have a few tables that have duplicated values from an import from a
> different database. I have two keys I tried to set as primary and got an
> error
> ERROR:  could not create unique index
> DETAIL:  Table contains duplicated values.
>
> Is there some join I can use to compare the hmhmkey, wmwmkey pairs against
> the table to find duplicate values? Each pair key should be unique but the
> old database was less than normalized.

Maybe
SELECT hmhmkey, wmwmkeyFROM exceptionsGROUP BY hmhmkey, wmwmkeyHAVING count(*)>1;

Kent Anderson wrote:

I have a few tables that have duplicated values from an import from a different database. I have two keys I tried to set as primary and got an error

ERROR:  could not create unique index
DETAIL:  Table contains duplicated values.

 

Is there some join I can use to compare the hmhmkey, wmwmkey pairs against the table to find duplicate values? Each pair key should be unique but the old database was less than normalized.

 

I was trying to use the code below but it returned no rows.

 

SELECT hmhmkey, wmwmkey
FROM   exceptions
EXCEPT
SELECT hmhmkey, wmwmkey

FROM  exceptions;

 

Any suggestions?

 

Kent Anderson

EZYield.com

407-629-0900

www.ezyield.com

 

========================================

This electronic message transmission contains information from the Company that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above.  If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited.  If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:" field.

 

******************************
This might do it...
If you do this on the table that had duplicates you wont need to join
select count(hmhmkey),count(wmwmkey) from
exceptions group by hmhmkey,wmwmkey having count(hmhmkey) >1 or having count(wmwmkey) >1; 

Kent Anderson wrote:

>  I have a few tables that have duplicated values from an import from a
> different database. I have two keys I tried to set as primary and got
> an error ERROR:  could not create unique index
> DETAIL:  Table contains duplicated values.Is there some join I can use
> to compare the hmhmkey, wmwmkey pairs against the table to find
> duplicate values? Each pair key should be unique but the old database
> was less than normalized.I was trying to use the code below but it
> returned no rows.SELECT hmhmkey, wmwmkey
> FROM   exceptions
> EXCEPT
> SELECT hmhmkey, wmwmkey FROM  exceptions;Any suggestions?Kent
> Anderson

You might want to search the [SQL] archive on the following topics
for more inspiration:

selecting duplicate records
Delete 1 Record of 2 Duplicate Records

Regards, Christoph

Kent Anderson wrote:
> I have a few tables that have duplicated values from an import from a 
> different database. I have two keys I tried to set as primary and got an 
> error
> ERROR:  could not create unique index
> DETAIL:  Table contains duplicated values.
>  
> Is there some join I can use to compare the hmhmkey, wmwmkey pairs 
> against the table to find duplicate values? Each pair key should be 
> unique but the old database was less than normalized.
>  
> I was trying to use the code below but it returned no rows.
>  
> SELECT hmhmkey, wmwmkey
> FROM   exceptions
> EXCEPT
> SELECT hmhmkey, wmwmkey
> FROM  exceptions;
>  
> Any suggestions?
>  
> Kent Anderson
> EZYield.com
> 407-629-0900
> www.ezyield.com <http://www.ezyield.com/>

Try http://archives.postgresql.org/pgsql-sql/1999-03/msg00239.php