Get duplicated records in a database table

05Apr09

I  had a situation, I wanted to find out all records in a single database table which occur more than once.

In one of the database tables there were some records that were repeating and I wanted to get all those records, how many times were repeating etc.

So here is the shortened version of my database table

CREATE TABLE HD_DESIG
(
DESIG_ID              NUMBER(10)              NOT NULL,
DESGIG_ORDER          NUMBER(2),
DESIG_ENAME           VARCHAR2(200 BYTE),
DESC_EN               VARCHAR2(300 BYTE),
)

First we will group all the same records and find the group count

SELECT HD_DESIG.desig_id,COUNT(*) cnt FROM HD_DESIG GROUP BY HD_DESIG.DESIG_ID;

Secondly, we will get only those records that have a count greater than one – only those records that exist more than once.

SELECT desig_id  FROM
(SELECT HD_DESIG.desig_id,COUNT(*) cnt FROM HD_DESIG GROUP BY HD_DESIG.DESIG_ID)
WHERE cnt > 1;

Now I’ll combine the above queries along with some more changes and we will get the information of all those records that are repeating.

Here is the whole query.

SELECT * FROM HD_DESIG WHERE HD_DESIG.DESIG_ID IN
(
SELECT desig_id  FROM
(SELECT HD_DESIG.desig_id,COUNT(*) cnt FROM HD_DESIG GROUP BY HD_DESIG.DESIG_ID)
WHERE cnt > 1
)

Advertisements


No Responses Yet to “Get duplicated records in a database table”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: