How to use RANK to Group Matched Records

Question

Long Story short. I have data that I'm trying to identify duplicate records by address. The address can be matched on the [Address] or [Remit_Address] fields. I use a JOIN and UNION to get the records, but I need the matched records to appear with each other in the results.

I can't sort by any of the existing fields, so a typical 'ORDER BY' won't work. I looked into RANK as suggested by someone and it looks like it might work, but I don't know how to do the Partition, and I think the Order gives me the same issue with ORDER BY.

If RANK is not the best option I'm open to other ideas. The goal ultimately is to group the matched records someway.

• SSMS 18
• SQL Server 2019

Here is the setup:

-- Output Table
CREATE TABLE [dupecheck] (
  [id] int identity(1, 1), 
  [Data Area] varchar(255), 
  [Supplier_No] varchar(255), 
  [Name] varchar(255), 
  [Address] varchar(255), 
  [City] varchar(255), 
  [State] varchar(255), 
  [Zip] varchar(255), 
  [Country] varchar(255), 
  [Remit_Address] varchar(255), 
  [Remit_City] varchar(255), 
  [Remit_State] varchar(255), 
  [Remit_Zip] varchar(255), 
  [Remit_Country] varchar(255), 
)


CREATE TABLE [sample_data] (
    [Supplier_No]           varchar(255),
    [Name]                  varchar(255),
    [Address]               varchar(255),
    [City]                  varchar(255),
    [State]                 varchar(255),
    [Zip]                   varchar(255),
    [Country]               varchar(255),
    [Remit_Address]         varchar(255),
    [Remit_City]            varchar(255),
    [Remit_State]           varchar(255),
    [Remit_Zip]             varchar(255),
    [Remit_Country]         varchar(255),
    [cleanAddress]          varchar(255),
    [cleanRemit_Address]    varchar(255),
    CONSTRAINT [suppliers_pk] PRIMARY KEY ([Supplier_No])
)

INSERT INTO [sample_data] VALUES
    ('1039104','Geez Companies','100 Aero Hudson Rd','Streetsboro','OH','44241','','100 Aero Hudson Road','Streetsboro','OH','44241','USA','100 Aero Hudson Rd','100 Aero Hudson Rd'),
    ('1218409','SouthWestern Medical','100 West Balor Ave','Osceola','AR','72370','USA','SouthWestern Medical100 W Balor Ave','Osceola','AR','72370','USA','100 W Balor Ave','SouthWestern Medical100 W Balor Ave'),
    ('1243789','SouthWestern Medical','100 West Balor Ave','Osceola','AR','72370','USA','SouthWestern Medical100 West Balor Ave','Osceola','AR','72370','USA','100 W Balor Ave','SouthWestern Medical100 W Balor Ave'),
    ('1243636','SIRI SYSTEMS','15 BRAD ROAD','WEXFORD','PA','15090','','','','','','','15 BRAD RD',''),
    ('1152482','FLEETWOOD MACK','22 WINDSOCK CT','ADDISON','IL','60101','','PO BOX 951','CHICAGO','IL','60694-5124','','22 WINDSOCK CT','PO BOX 951'),
    ('1224483','Aerospace Junction','211500 Communicate Ave','Mingo Junction','OH','43939','USA','P O Box 99','Mingo Junction','OH','43939','USA','211500 Communicate Ave','PO Box 99'),
    ('1243397','Squeezy Felt','SCHREIBER DIST','NEW KENSINGTON','PA','15068','','','','','','','SCHREIBER DIST',''),
    ('1230895','NERO CO','28 North US State Highway 99','Osceola','AR','72370','USA','PO Box 204','Cape Girardeau','MO','63702-2045','USA','28 N US State Hwy 99','PO Box 204'),
    ('1243782','NERO CO','28 North US State Highway 99','Osceola','AR','72370','USA','PO Box 204','Cape Girardeau','MO','63702-2045','USA','28 N US State Hwy 99','PO Box 204'),
    ('1135880','RICHARD PRYOR SEMINARS','PO BOX 2194','KANSAS CITY','MO','64121-9468','USA','RICHARD PRYOR SEMINARS P O BOX 2194','KANSAS CITY','MO','64121-9468','USA','PO BOX 2194','RICHARD PRYOR SEMINARS PO BOX 2194'),
    ('1241328','INFINITY AND BEYOND','P.O. BOX 169','GASTONIA','NC','28053-0269','USA','','','','','','PO BOX 169',''),
    ('1259522','ZEEBO INC','PO BOX 169','GASSTONIA','NC','28053-0269','USA','','','','','','PO BOX 169',''),
    ('1255253','AT&T','PO Box 50221','Carol Stream','IL','60197','USA','','','','','','PO Box 50221',''),
    ('1135513','AT&T','PO Box 50221','Carol Stream','IL','60197-5080','USA','','','','','','PO Box 50221',''),
    ('1119161','Machine Co, Inc','3306 N Thorne Blvd','Chattanooga','TN','','','PO BOX 5301','CHATTANOOGA','TN','37406','USA','3306 N Thorne Blvd','PO BOX 5301'),
    ('1176587','Topsy Turvy','365 Welmington Road','Chicago','IL','60606','USA','','','','','','365 Welmington Rd',''),
    ('2156671','Topsy Turvvy, Inc.','P.O. Box 55217','Columbus','OH','43081','','365 Welmington Road','Chicago','IL','60606','USA','','365 Welmington Rd')


CREATE TABLE [dupe_addresses](
    [NewAdd] [varchar](255) NULL
)

INSERT INTO [dupe_addresses] VALUES
    ('100 W Balor Ave'),
    ('28 N US State Hwy 99'),
    ('365 Welmington Rd'),
    ('PO BOX 169'),
    ('PO Box 204'),
    ('PO Box 50221'),
    ('SouthWestern Medical100 W Balor Ave')

Existing Query:

INSERT INTO [dupecheck]
    SELECT * FROM (
    SELECT 
        'Address Match' AS [Reason], 
        pv.[Supplier_No], 
        pv.[Name], 
        pv.[Address], 
        pv.[City], 
        pv.[State], 
        pv.[Zip], 
        pv.[Country], 
        pv.[Remit_Address], 
        pv.[Remit_City], 
        pv.[Remit_State], 
        pv.[Remit_Zip], 
        pv.[Remit_Country]
         FROM [dupe_addresses] n 
      LEFT JOIN [sample_data] pv 
        ON 
        (n.[NewAdd] = pv.[cleanAddress] AND ( [Address] <> '' AND [Address] IS NOT NULL ) )
       WHERE ([Supplier_No] IS NOT NULL AND [Supplier_No] <> '') 

    UNION

    SELECT 
        'Address Match' AS [Reason], 
        pv.[Supplier_No], 
        pv.[Name], 
        pv.[Address], 
        pv.[City], 
        pv.[State], 
        pv.[Zip], 
        pv.[Country], 
        pv.[Remit_Address], 
        pv.[Remit_City], 
        pv.[Remit_State], 
        pv.[Remit_Zip], 
        pv.[Remit_Country]
         FROM [dupe_addresses] n 
      LEFT JOIN [sample_data] pv 
        ON 
        (n.[NewAdd] = pv.[cleanRemit_Address] AND ( [Remit_Address] <> '' AND [Remit_Address] IS NOT NULL) )
       WHERE ([Supplier_No] IS NOT NULL AND [Supplier_No] <> '') 
       ) q1

Current Results:

Reason  Supplier_No Name    Address City    State   Zip Country Remit_Address   Remit_City  Remit_State Remit_Zip   Remit_Country
Address Match   1135513 AT&T    PO Box 50221    Carol Stream    IL  60197-5080  USA                 
Address Match   1176587 Topsy Turvy 365 Welmington Road Chicago IL  60606   USA                 
Address Match   1218409 SouthWestern Medical    100 West Balor Ave  Osceola AR  72370   USA SouthWestern Medical100 W Balor Ave Osceola AR  72370   USA
Address Match   1230895 NERO CO 28 North US State Highway 99    Osceola AR  72370   USA PO Box 204  Cape Girardeau  MO  63702-2045  USA
Address Match   1241328 INFINITY AND BEYOND P.O. BOX 169    GASTONIA    NC  28053-0269  USA                 
Address Match   1243782 NERO CO 28 North US State Highway 99    Osceola AR  72370   USA PO Box 204  Cape Girardeau  MO  63702-2045  USA
Address Match   1243789 SouthWestern Medical    100 West Balor Ave  Osceola AR  72370   USA SouthWestern Medical100 West Balor Ave  Osceola AR  72370   USA
Address Match   1255253 AT&T    PO Box 50221    Carol Stream    IL  60197   USA                 
Address Match   1259522 ZEEBO INC   PO BOX 169  GASSTONIA   NC  28053-0269  USA                 
Address Match   2156671 Topsy Turvvy, Inc.  P.O. Box 55217  Columbus    OH  43081       365 Welmington Road Chicago IL  60606   USA

Desired Results:

Reason  Supplier_No Name    Address City    State   Zip Country Remit_Address   Remit_City  Remit_State Remit_Zip   Remit_Country   rank
Address Match   1135513 AT&T    PO Box 50221    Carol Stream    IL  60197-5080  USA                     1
Address Match   1255253 AT&T    PO Box 50221    Carol Stream    IL  60197   USA                     1
Address Match   1241328 INFINITY AND BEYOND P.O. BOX 169    GASTONIA    NC  28053-0269  USA                     2
Address Match   1259522 ZEEBO INC   PO BOX 169  GASSTONIA   NC  28053-0269  USA                     2
Address Match   1243782 NERO CO 28 North US State Highway 99    Osceola AR  72370   USA PO Box 204  Cape Girardeau  MO  63702-2045  USA 3
Address Match   1230895 NERO CO 28 North US State Highway 99    Osceola AR  72370   USA PO Box 204  Cape Girardeau  MO  63702-2045  USA 3
Address Match   1218409 SouthWestern Medical    100 West Balor Ave  Osceola AR  72370   USA SouthWestern Medical100 W Balor Ave Osceola AR  72370   USA 4
Address Match   1243789 SouthWestern Medical    100 West Balor Ave  Osceola AR  72370   USA SouthWestern Medical100 West Balor Ave  Osceola AR  72370   USA 4
Address Match   2156671 Topsy Turvvy, Inc.  P.O. Box 55217  Columbus    OH  43081       365 Welmington Road Chicago IL  60606   USA 5
Address Match   1176587 Topsy Turvy 365 Welmington Road Chicago IL  60606   USA                     5

Answer 1

This query creates the desired result.

with cte as (
    select s2.NewAdd grp, s1.*
        , rank() over(partition by Supplier_No order by s2.NewAdd) rnk
    from sample_data s1
    inner join dupe_addresses s2 on  
        (s1.cleanAddress=s2.newAdd) or (s1.cleanRemit_Address=s2.newAdd)
)
select c1.*
from cte c1
where rnk = 1
order by c1.grp

removed the Union , and combine two join Conditions by OR instead. so a record with both conditions may be found.

The rank() is used to calculate a rank for each row within a partition of a result set.

partition by Supplier_No used to To identify duplicate records.

finally, use the where rnk = 1 to see the group of records without repetition.

Answer 2

I'm sure there is a shorter/cleaner way to do this but while I wait for my coffee to kick in the below should do what you want.

SELECT s1.*
    ,coalesce((
            SELECT s1.Cleanaddress
            FROM dupe_addresses s2
            WHERE s1.cleanAddress = s2.newAdd
            ), (
            SELECT s1.cleanRemit_Address
            FROM dupe_addresses s2
            WHERE s1.cleanRemit_Address = s2.newAdd
            )) AS MatchedAddress
FROM sample_data s1
WHERE EXISTS (
        SELECT 1
        FROM dupe_addresses s2
        WHERE (s1.cleanAddress = s2.newAdd)
            OR (s1.cleanRemit_Address = s2.newAdd)
        )
ORDER BY MatchedAddress

Edit: I've thought about this a bit more. I would change the way your doing this since you say you have more criteria for matching this would be a better way of achieving what you want. Basically I would create a CleanedAddressID on your supplier/data table and then put all your cleaned addresses into a cleaned address table.

Once you've done that you can update the CleanedAddressID and you can use more criteria/matching than you are currently using.

The following code should help you out and the final query will return all your duplicates based on address.

As time goes on you could add different matches in a similar way and then create a duplication score. I know that's outside the context of your question but I thought I'd mention it since it shows how this more dynamic solution makes it easier to expand upon.

I've left the above solution is as you said this did what you want and I'll let you digest it but it's messy and would get messier with more criteria.

CREATE TABLE [CleanedAddresses] (
    ID INT IDENTITY(1, 1)
    ,[Address] [varchar](255) NOT NULL UNIQUE
    ,PRIMARY KEY (ID)
    )

INSERT INTO [CleanedAddresses] ([Address])
VALUES ('100 W Balor Ave')
    ,('28 N US State Hwy 99')
    ,('365 Welmington Rd')
    ,('PO BOX 169')
    ,('PO Box 204')
    ,('PO Box 50221')
    ,('SouthWestern Medical100 W Balor Ave')

CREATE TABLE [sample_data] (
    [Supplier_No] VARCHAR(255)
    ,[Name] VARCHAR(255)
    ,[Address] VARCHAR(255)
    ,[City] VARCHAR(255)
    ,[State] VARCHAR(255)
    ,[Zip] VARCHAR(255)
    ,[Country] VARCHAR(255)
    ,[Remit_Address] VARCHAR(255)
    ,[Remit_City] VARCHAR(255)
    ,[Remit_State] VARCHAR(255)
    ,[Remit_Zip] VARCHAR(255)
    ,[Remit_Country] VARCHAR(255)
    ,[cleanAddress] VARCHAR(255)
    ,[cleanRemit_Address] VARCHAR(255)
    ,CleanAddressID INT NULL CONSTRAINT [suppliers_pk] PRIMARY KEY ([Supplier_No])
    ,FOREIGN KEY (CleanAddressID) REFERENCES [CleanedAddresses](ID)
    )

INSERT INTO [sample_data] (
    [Supplier_No]
    ,[Name]
    ,[Address]
    ,[City]
    ,[State]
    ,[Zip]
    ,[Country]
    ,[Remit_Address]
    ,[Remit_City]
    ,[Remit_State]
    ,[Remit_Zip]
    ,[Remit_Country]
    ,[cleanAddress]
    ,[cleanRemit_Address]
    )
VALUES (
    '1039104'
    ,'Geez Companies'
    ,'100 Aero Hudson Rd'
    ,'Streetsboro'
    ,'OH'
    ,'44241'
    ,''
    ,'100 Aero Hudson Road'
    ,'Streetsboro'
    ,'OH'
    ,'44241'
    ,'USA'
    ,'100 Aero Hudson Rd'
    ,'100 Aero Hudson Rd'
    )
    ,(
    '1218409'
    ,'SouthWestern Medical'
    ,'100 West Balor Ave'
    ,'Osceola'
    ,'AR'
    ,'72370'
    ,'USA'
    ,'SouthWestern Medical100 W Balor Ave'
    ,'Osceola'
    ,'AR'
    ,'72370'
    ,'USA'
    ,'100 W Balor Ave'
    ,'SouthWestern Medical100 W Balor Ave'
    )
    ,(
    '1243789'
    ,'SouthWestern Medical'
    ,'100 West Balor Ave'
    ,'Osceola'
    ,'AR'
    ,'72370'
    ,'USA'
    ,'SouthWestern Medical100 West Balor Ave'
    ,'Osceola'
    ,'AR'
    ,'72370'
    ,'USA'
    ,'100 W Balor Ave'
    ,'SouthWestern Medical100 W Balor Ave'
    )
    ,(
    '1243636'
    ,'SIRI SYSTEMS'
    ,'15 BRAD ROAD'
    ,'WEXFORD'
    ,'PA'
    ,'15090'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'15 BRAD RD'
    ,''
    )
    ,(
    '1152482'
    ,'FLEETWOOD MACK'
    ,'22 WINDSOCK CT'
    ,'ADDISON'
    ,'IL'
    ,'60101'
    ,''
    ,'PO BOX 951'
    ,'CHICAGO'
    ,'IL'
    ,'60694-5124'
    ,''
    ,'22 WINDSOCK CT'
    ,'PO BOX 951'
    )
    ,(
    '1224483'
    ,'Aerospace Junction'
    ,'211500 Communicate Ave'
    ,'Mingo Junction'
    ,'OH'
    ,'43939'
    ,'USA'
    ,'P O Box 99'
    ,'Mingo Junction'
    ,'OH'
    ,'43939'
    ,'USA'
    ,'211500 Communicate Ave'
    ,'PO Box 99'
    )
    ,(
    '1243397'
    ,'Squeezy Felt'
    ,'SCHREIBER DIST'
    ,'NEW KENSINGTON'
    ,'PA'
    ,'15068'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'SCHREIBER DIST'
    ,''
    )
    ,(
    '1230895'
    ,'NERO CO'
    ,'28 North US State Highway 99'
    ,'Osceola'
    ,'AR'
    ,'72370'
    ,'USA'
    ,'PO Box 204'
    ,'Cape Girardeau'
    ,'MO'
    ,'63702-2045'
    ,'USA'
    ,'28 N US State Hwy 99'
    ,'PO Box 204'
    )
    ,(
    '1243782'
    ,'NERO CO'
    ,'28 North US State Highway 99'
    ,'Osceola'
    ,'AR'
    ,'72370'
    ,'USA'
    ,'PO Box 204'
    ,'Cape Girardeau'
    ,'MO'
    ,'63702-2045'
    ,'USA'
    ,'28 N US State Hwy 99'
    ,'PO Box 204'
    )
    ,(
    '1135880'
    ,'RICHARD PRYOR SEMINARS'
    ,'PO BOX 2194'
    ,'KANSAS CITY'
    ,'MO'
    ,'64121-9468'
    ,'USA'
    ,'RICHARD PRYOR SEMINARS P O BOX 2194'
    ,'KANSAS CITY'
    ,'MO'
    ,'64121-9468'
    ,'USA'
    ,'PO BOX 2194'
    ,'RICHARD PRYOR SEMINARS PO BOX 2194'
    )
    ,(
    '1241328'
    ,'INFINITY AND BEYOND'
    ,'P.O. BOX 169'
    ,'GASTONIA'
    ,'NC'
    ,'28053-0269'
    ,'USA'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'PO BOX 169'
    ,''
    )
    ,(
    '1259522'
    ,'ZEEBO INC'
    ,'PO BOX 169'
    ,'GASSTONIA'
    ,'NC'
    ,'28053-0269'
    ,'USA'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'PO BOX 169'
    ,''
    )
    ,(
    '1255253'
    ,'AT&T'
    ,'PO Box 50221'
    ,'Carol Stream'
    ,'IL'
    ,'60197'
    ,'USA'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'PO Box 50221'
    ,''
    )
    ,(
    '1135513'
    ,'AT&T'
    ,'PO Box 50221'
    ,'Carol Stream'
    ,'IL'
    ,'60197-5080'
    ,'USA'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'PO Box 50221'
    ,''
    )
    ,(
    '1119161'
    ,'Machine Co, Inc'
    ,'3306 N Thorne Blvd'
    ,'Chattanooga'
    ,'TN'
    ,''
    ,''
    ,'PO BOX 5301'
    ,'CHATTANOOGA'
    ,'TN'
    ,'37406'
    ,'USA'
    ,'3306 N Thorne Blvd'
    ,'PO BOX 5301'
    )
    ,(
    '1176587'
    ,'Topsy Turvy'
    ,'365 Welmington Road'
    ,'Chicago'
    ,'IL'
    ,'60606'
    ,'USA'
    ,''
    ,''
    ,''
    ,''
    ,''
    ,'365 Welmington Rd'
    ,''
    )
    ,(
    '2156671'
    ,'Topsy Turvvy, Inc.'
    ,'P.O. Box 55217'
    ,'Columbus'
    ,'OH'
    ,'43081'
    ,''
    ,'365 Welmington Road'
    ,'Chicago'
    ,'IL'
    ,'60606'
    ,'USA'
    ,''
    ,'365 Welmington Rd'
    )

UPDATE S
SET CleanAddressID = c.ID
FROM Sample_data S
INNER JOIN CleanedAddresses C ON c.Address = s.cleanAddress

UPDATE S
SET CleanAddressID = c.ID
FROM Sample_data S
INNER JOIN CleanedAddresses C ON c.Address = s.cleanRemit_Address
WHERE s.CleanAddressID IS NULL

SELECT *
FROM Sample_data S
WHERE CleanAddressID IS NOT NULL
    AND cleanAddressID IN (
        SELECT s2.cleanAddressID
        FROM sample_data s2
        GROUP BY s2.cleanAddressID
        HAVING count(*) > 1
        )
ORDER BY CleanAddressID

Answer 3

First of all you can avoid using of expensive union by mentioning both the condition in on clause like below:

  ON 
  (n.[NewAdd] = pv.[cleanAddress] AND ( [Address] <> '' AND [Address] IS NOT NULL ) )
  or 
  (n.[NewAdd] = pv.[cleanRemit_Address] AND ( [Remit_Address] <> '' AND [Remit_Address] IS NOT NULL) )

Then you can remove duplicate rows per supplier_no with the help of row_number()over() window function.

Then rank those rows according to their addreses with

dense_rank()over(order by case when(n.[NewAdd] = pv.[cleanAddress] AND ( [Address] < '' AND [Address] IS NOT NULL ) )
             then cleanaddress else Remit_Address end)

But I could not understand how you formed group 4 with four rows.

Query:

 with cte 
 as
 (
     SELECT 
         'Address Match' AS [Reason], 
         pv.[Supplier_No], 
         pv.[Name], 
         pv.[Address], 
         pv.[City], 
         pv.[State], 
         pv.[Zip], 
         pv.[Country], 
         pv.[Remit_Address], 
         pv.[Remit_City], 
         pv.[Remit_State], 
         pv.[Remit_Zip], 
         pv.[Remit_Country],
         row_number()over (partition by supplier_no order by address  ,remit_address )rn,
         dense_rank()over(order by case when(n.[NewAdd] = pv.[cleanAddress] AND ( [Address] < '' AND [Address] IS NOT NULL ) )
         then cleanaddress else Remit_Address end) rnk
          FROM [dupe_addresses] n 
       LEFT JOIN [sample_data] pv 
         ON 
         (n.[NewAdd] = pv.[cleanAddress] AND ( [Address] < '' AND [Address] IS NOT NULL ) )
         or 
          (n.[NewAdd] = pv.[cleanRemit_Address] AND ( [Remit_Address] < '' AND [Remit_Address] IS NOT NULL) )
        WHERE [Supplier_No] IS NOT NULL AND [Supplier_No] < ''
 )
 select * from cte where rn=1
 order by rnk desc

Reason	Supplier_No	Name	Address	City	State	Zip	Country	Remit_Address	Remit_City	Remit_State	Remit_Zip	Remit_Country	rn	rnk
Address Match	1135513	AT&T	PO Box 50221	Carol Stream	IL	60197-5080	USA						1	7
Address Match	1255253	AT&T	PO Box 50221	Carol Stream	IL	60197	USA						1	7
Address Match	1259522	ZEEBO INC	PO BOX 169	GASSTONIA	NC	28053-0269	USA						1	5
Address Match	1241328	INFINITY AND BEYOND	P.O. BOX 169	GASTONIA	NC	28053-0269	USA						1	5
Address Match	2156671	Topsy Turvvy, Inc.	P.O. Box 55217	Columbus	OH	43081		365 Welmington Road	Chicago	IL	60606	USA	1	4
Address Match	1176587	Topsy Turvy	365 Welmington Road	Chicago	IL	60606	USA						1	3
Address Match	1230895	NERO CO	28 North US State Highway 99	Osceola	AR	72370	USA	PO Box 204	Cape Girardeau	MO	63702-2045	USA	1	2
Address Match	1243782	NERO CO	28 North US State Highway 99	Osceola	AR	72370	USA	PO Box 204	Cape Girardeau	MO	63702-2045	USA	1	2
Address Match	1218409	SouthWestern Medical	100 West Balor Ave	Osceola	AR	72370	USA	SouthWestern Medical100 W Balor Ave	Osceola	AR	72370	USA	1	1
Address Match	1243789	SouthWestern Medical	100 West Balor Ave	Osceola	AR	72370	USA	SouthWestern Medical100 West Balor Ave	Osceola	AR	72370	USA	1	1

db<fiddle here