SQL Server - Check duplicate data in two concurrent fields

5

DBMS: SQL Server 2014;

Problem: I need to know which records are duplicated, so that the verification is done in two simultaneous fields, ie if the field "CPF" with the value '83971465842' and the field 'MAT' with the value ' 001 'exists, in an identical fashion, in another tuple. As shown in the example below:

CPF           MAT
83971465842   001
83971465842   001
57650942287   111
57655707200   305
57656959220   916
57656959220   209

Note that in the first two records, the values '83971465842' + '001' are on two different lines. These are precisely the ones I need to identify. How to make the query?

    
asked by anonymous 25.06.2014 / 22:39

2 answers

13
select CPF, MAT, count(*)
from Tabela
group by CPF, MAT
having count(*) > 1
    
25.06.2014 / 22:43
4
Select *
From tabela t1
Where       exists (select CPF, MAT
                    from Tabela t2
                    where t2.cpf = t1.cpf
                    and   t2.mat = t1.mat
                    group by CPF, MAT 
                    having count(*) > 1)

By expanding the answer above by listing all records in the duplicate table, the next step, I believe, would be some action on the duplicates, deleting or changing, this query is the basis for this.

    
26.06.2014 / 00:36