Finding duplicated values

This is a multi-part message in MIME format.

------=_NextPart_000_0002_01C4B77E.7AC7C010
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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.

------=_NextPart_000_0002_01C4B77E.7AC7C010
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1476" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798035318-21102004>I have a =
few tables
that have duplicated values from an import from a different datab=
ase.
I have two keys I tried to set as primary and got an error </SPAN></FONT></=
DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798035318-21102004>ERROR:&nb=
sp; could
not create unique index<BR>DETAIL:  Table contains duplicated
values.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN
class=3D798035318-21102004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798035318-21102004>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 l=
ess
than normalized.</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN
class=3D798035318-21102004></SPAN></FONT> </DIV><SPAN
class=3D798035318-21102004><FONT face=3DArial size=3D2>I was trying to use =
the code
below but it returned no rows.</FONT></SPAN>
<DIV><FONT face=3DArial size=3D2><SPAN
class=3D798035318-21102004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798035318-21102004>SELECT hm=
hmkey,
wmwmkey <BR>FROM   exceptions<BR>EXCEPT<BR>SELECT hmhmkey, wmwmke=
y
</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798035318-21102004>FROM =
;
exceptions;</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN
class=3D798035318-21102004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798035318-21102004>Any
suggestions?</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN
class=3D798035318-21102004></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>Kent Anderson</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>EZYield.com</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>407-629-0900</FONT></DIV>
<DIV><FONT face=3DArial size=3D2><A
href=3D"http://www.ezyield.com/">www.ezyield.com</A></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial
size=3D2>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>This electronic message transmission conta=
ins
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 t=
hat
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.</FONT></DIV>
<DIV> </DIV></BODY></HTML>

------=_NextPart_000_0002_01C4B77E.7AC7C010--
kenta [ Do, 21 Oktober 2004 20:58 ] [ ID #453862 ]

Re: Finding duplicated values

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.ph p
Bricklen [ Do, 21 Oktober 2004 21:20 ] [ ID #453863 ]

Re: Finding duplicated values

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, wmwmkey
FROM exceptions
GROUP BY hmhmkey, wmwmkey
HAVING count(*)>1;

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
sszabo [ Do, 21 Oktober 2004 22:09 ] [ ID #453864 ]

Re: Finding duplicated values

This is a multi-part message in MIME format.
--------------000104040401020407000003
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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/>
>
> ========================================
> 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;



--------------000104040401020407000003
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Kent Anderson wrote:<br>
<blockquote cite="midLPENJIOOLAIJBFKIBDKOIEAHFPAD.kenta [at] ezyield.com"
type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta content="MSHTML 6.00.2800.1476" name="GENERATOR">
<div><font face="Arial" size="2"><span class="798035318-21102004">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 </span></font></div>
<div><font face="Arial" size="2"><span class="798035318-21102004">ERROR: 
could not create unique index<br>
DETAIL:  Table contains duplicated values.</span></font></div>
<div><font face="Arial" size="2"><span class="798035318-21102004"></span></font> </div>
<div><font face="Arial" size="2"><span class="798035318-21102004">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.</span></font></div>
<div><font face="Arial" size="2"><span class="798035318-21102004"></span></font> </div>
<span class="798035318-21102004"><font face="Arial" size="2">I was
trying to use the code below but it returned no rows.</font></span>
<div><font face="Arial" size="2"><span class="798035318-21102004"></span></font> </div>
<div><font face="Arial" size="2"><span class="798035318-21102004">SELECT
hmhmkey, wmwmkey <br>
FROM   exceptions<br>
EXCEPT<br>
SELECT hmhmkey, wmwmkey </span></font></div>
<div><font face="Arial" size="2"><span class="798035318-21102004">FROM 
exceptions;</span></font></div>
<div><font face="Arial" size="2"><span class="798035318-21102004"></span></font> </div>
<div><font face="Arial" size="2"><span class="798035318-21102004">Any
suggestions?</span></font></div>
<div><font face="Arial" size="2"><span class="798035318-21102004"></span></font> </div>
<div><font face="Arial" size="2">Kent Anderson</font></div>
<div><font face="Arial" size="2">EZYield.com</font></div>
<div><font face="Arial" size="2">407-629-0900</font></div>
<div><font face="Arial" size="2">www.ezyield.com</font></div>
<div> </div>
<div><font face="Arial" size="2">========================================</font></div>
<div><font face="Arial" size="2">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.</font></div>
<div> </div>
</blockquote>
******************************<br>
This might do it... <br>
If you do this on the table that had duplicates you wont need to join<br>
select count(hmhmkey),count(wmwmkey) from <br>
exceptions group by hmhmkey,wmwmkey having count(hmhmkey) >1 or
having count(wmwmkey) >1;  <br>
<br>
<br>
</body>
</html>

--------------000104040401020407000003--
lorid [ Fr, 22 Oktober 2004 01:18 ] [ ID #453868 ]

Re: Finding duplicated values

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



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org
ch [ Fr, 22 Oktober 2004 09:49 ] [ ID #453869 ]
Datenbanken » comp.databases.postgresql.sql » Finding duplicated values

Vorheriges Thema: Re: Functions return a select in a table, which data type I
Nächstes Thema: Trouble with explicit joins

[ Startseite ] [ Administrator ] [ Suche ] [ Hinweise ] [ Impressum ]

Powered by FudForum