SQL Query To Find Duplicate Rows In A Oracle Database Table

SQL Query To Find Duplicate Rows In A Oracle Database Table

This Post Presents A  Demo to find duplicate records by running a SQL Query  In A Oracle DB (12c) Table.

Oracle DB Used : Oracle Database 12c Enterprise Edition Release

Suppose A Database Table is Like :

CREATE TABLE DUPLICATE_TEST
(
  A                VARCHAR2(10),
  B                VARCHAR2(10),
  C                VARCHAR2(10),
  D                VARCHAR2(10),
  E                VARCHAR2(10),
  F                NUMBER(5)
)
/

(This table is not with any unique or primary key constraints)

Have following Rows (Three Rows with field value 1,2,3,4,5,6 each), So 3 Duplicate Records (Rows):

A,B,C,D,E,F

1 2 3 4 5 6
1 2 3 4 5 6
1 2 3 4 5 6

 

On executing the following :

SELECT
    A, B,C,D,E,F, count(*) no_of_records
FROM DUPLICATE_TEST
GROUP BY A, B,C,D,E,F
HAVING count(*) > 1;

 

Would Return The Following :

A,B,C,D,E,F,NO_OF_RECORDS

1 2 3 4 5 6 3


See Last Value As 3 :NO_OF_RECORDS

Here are the relevant screenshots :

Duplicate Records Count in Oracle DB Table

Duplicate Records Count in Oracle DB Table

Inserted Duplicate Rows in A Table Of Oracle DB
Inserted Duplicate Rows in A Table Of Oracle DB

 

One thought on “SQL Query To Find Duplicate Rows In A Oracle Database Table

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s