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
)