CODEWITHSUNDEEP
sqlduplicates
mary
mary

Reputation: 19

SQL - Numerate duplicate records by group

We appreciated your help in advance. We should numerate the duplicated records per group found based on CustLastname, CustFirstName, DealerNumber, ProductCode and Vin number.

This is the table content.

sCustLastName sCustFirstName sDealerNumber sProductCode  sVIN
    Ln1             Fn1         TWS00967        GAP     1HGCR2F5XHA151050
    Ln1             Fn1         TWS00967        GAP     1HGCR2F5XHA151050
    Ln1             Fn1         TWS00967        GAP     1HGCR2F5XHA151050
    Ln2             Fn2         TWS00968        GAP     WVWEV7AJXAW197040
    Ln2             Fn2         TWS00968        GAP     WVWEV7AJXAW197040
    Ln3             Fn3         TWS00967        GAP     2HGFC2F56GH536572
    Ln3             Fn3         TWS00967        GAP     2HGFC2F56GH536572
    Ln4             Fn4         TWS00967        PPM     19XFC2F76JE003513
    Ln4             Fn4         TWS00967        PPM     19XFC2F76JE003513
    Ln4             Fn4         TWS00967        PPM     19XFC2F76JE003513

Result Desired:

Count sCustLastName sCustFirstName sDealerNumber sProductCode  sVIN
1   Ln1             Fn1         TWS00967        GAP     1HGCR2F5XHA151050
2   Ln1             Fn1         TWS00967        GAP     1HGCR2F5XHA151050
3   Ln1             Fn1         TWS00967        GAP     1HGCR2F5XHA151050
1   Ln2             Fn2         TWS00968        GAP     WVWEV7AJXAW197040
2   Ln2             Fn2         TWS00968        GAP     WVWEV7AJXAW197040
1   Ln3             Fn3         TWS00967        GAP     2HGFC2F56GH536572
2   Ln3             Fn3         TWS00967        GAP     2HGFC2F56GH536572
1   Ln4             Fn4         TWS00967        PPM     19XFC2F76JE003513
2   Ln4             Fn4         TWS00967        PPM     19XFC2F76JE003513
3   Ln4             Fn4         TWS00967        PPM     19XFC2F76JE003513

Here is the code to create the table:

Create Table Contract_Header
(
sCustLastName   varchar(20),
sCustFirstName  varchar(20),
sDealerNumber   nvarchar(20),
sProductCode    varchar(3),
sVIN    nvarchar(20)
)

Here is the code to insert the data:
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln1','Fn1','TWS00967','GAP','1HGCR2F5XHA151050')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln1','Fn1','TWS00967','GAP','1HGCR2F5XHA151050')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln1','Fn1','TWS00967','GAP','1HGCR2F5XHA151050')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln2','Fn2','TWS00968','GAP','WVWEV7AJXAW197040')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln2','Fn2','TWS00968','GAP','WVWEV7AJXAW197040')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln3','Fn3','TWS00967','GAP','2HGFC2F56GH536572')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln3','Fn3','TWS00967','GAP','2HGFC2F56GH536572')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln4','Fn4','TWS00967','PPM','19XFC2F76JE003513')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln4','Fn4','TWS00967','PPM','19XFC2F76JE003513')
Insert into Contract_Header (sCustLastName,sCustFirstName,sDealerNumber,sProductCode,sVIN) Values ('Ln4','Fn4','TWS00967','PPM','19XFC2F76JE003513')

Sorry I did not know how to make the text more readable...

Thanks very much,

Mary

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You are looking for row_number():

select t.*
       row_number() over (partition by CustLastname, CustFirstName, DealerNumber, ProductCode, Vin order by vin) as seqnum
from t;

Upvotes: 1

Related Questions