1

I have a scenario that I need to "match" records based on multiple attributes of a person. For instance, if a FirstName and LastName match, or a NickName and LastName match, those two scenarios should be grouped into one larger match. Here's the example data in SQLFiddle: http://www.sqlfiddle.com/#!18/0ca91/7

I'm generating a match key from the record attributes. The result gives me two different match keys and three total records. I need a result that has only one match key generated, and eventually I'm going to group all three records into one golden record in a separate step. I cannot figure out a way to logically group these records together either by "group by" or by using DENSE_RANK to generate my match key. Any help would be greatly appreciated! Thanks!

CREATE TABLE Persons (
    ID int,
    FirstName varchar(255),  
    LastName varchar(255),    
    NickName varchar(255)
);

INSERT INTO Persons
SELECT 1 AS ID, 'NIKKI' AS FNAME, 'MADISON' AS LNAME, 'Nikki' AS NickName
UNION ALL
SELECT 2 AS ID, 'NICOLE' AS FNAME, 'MADISON' AS LNAME, 'NICOLE' AS NickName
UNION ALL
SELECT  3 AS ID, 'NICOLE' AS FNAME, 'MADISON' AS LNAME,'Nikki' AS NickName

SELECT 
* 
, DENSE_RANK() OVER (ORDER BY TRIM(LastName), TRIM(FirstName)) AS GroupKey
FROM Persons

Desired Result:

GroupKey
1
1
1
CodeMonkey
  • 413
  • 4
  • 15
  • Soundex will not work for first name/nickname matching. This question is similar to yours: https://stackoverflow.com/questions/2381522/database-of-common-name-aliases-nicknames-of-people – Alex Apr 11 '18 at 02:05
  • Thanks for the response. I'm aware soundex won't work. I am using it to combat typos because the data I have is not clean. It's not to try and use for nicknames. – CodeMonkey Apr 11 '18 at 14:03

0 Answers0