I need an elegant SQL query to check that a group of records in a table has identical duplicates (same values for all attributes/columns except the primary key) in a another group of records (identified with their primary key) of the same table.

Question

I need an elegant SQL query to check that a group of distinct records in a table has identical duplicates (same values for all attributes/columns except the primary key) in a another group of records (identified with their primary key) of the same table.

Answer 1

Is it nessecary to write a long text into post subject, instead the body?

That's really to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.

Answer 2

Hi @Paul Personne

You provide too little information.

It's not clear to me if you want to check if a set of data has duplicates in the table other than the primary key.

If so, you can take a look at the following example.

create table tabletest(id int primary key,num1 int,num2 int,num3 int);
insert into tabletest values
(1,3,5,7),
(2,5,6,7),
(3,3,5,7),
(4,5,6,7),
(5,3,5,7),
(6,7,8,9),
(7,5,6,7),
(8,10,9,2),
(9,7,8,9);
select *,count(*)over(partition by num1,num2,num3) as counts from tabletest;

The nums column will tell you how many duplicates there are in addition to the primary key.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.