Path: utzoo!utgpu!jarvis.csri.toronto.edu!clyde.concordia.ca!uunet!samsung!usc!ucsd!ucsdhub!hp-sdd!ncr-sd!ncrlnk!usglnk!usperb!daved
From: daved@usperb.Dayton.NCR.COM (Dave Dresselhouse)
Newsgroups: comp.databases
Subject: Re: SQL query for duplicates
Keywords: ORACLE, SQL
Message-ID: <465@usperb.Dayton.NCR.COM>
Date: 22 Dec 89 04:53:28 GMT
References: <5784@uhccux.uhcc.hawaii.edu>
Reply-To: daved@usperb.Dayton.NCR.COM (Dave Dresselhouse)
Organization: NCR Corporation - U.S. Group
Lines: 24
Summary:
Expires:
Followup-To:

In article <5784@uhccux.uhcc.hawaii.edu> larned@uhccux.uhcc.hawaii.edu (Paul Larned) writes:
>I am starting an application in Oracle v.5.1b Prof. for MS-DOS for a
>network of PCs which will use RDBMS v.6 for OS/2--this after years of
>writing applications in a dBASE environment.  I am importing a fixed-
>column data file from a UNIFY database into an ORACLE-created table.
>The primary key field I want to use has several duplicates in it from
>an unprotected UNIFY environment.  I am having trouble writing a SQL
>statement to identify the duplicate 4-digit numbers (in a character
>field) in the new database so that they can be changed or deleted.  I
>hope I am not reduced to listing all rows and visually identifying the
>duplicates.  There are about 1000 rows.  I would appreciate any
>suggestions.
Try This:
SELECT <KEY_COLUMN>, COUNT(*)
FROM <TABLE>
GROUP BY <KEY_COLUMN>
HAVING COUNT(*) > 1
...This should do the trick. Your output will list only duplicate key
values, along with the number of duplicates for each key.
-----------------------------------------------------------------------------
Dave Dresselhouse                          dave.dresselhouse@Dayton.NCR.COM
NCR Corporation                            (513) 445-4449
Dayton, OH
-----------------------------------------------------------------------------