Here you will find a solution:
DECLARE @Employee TABLE
(
Fullname VARCHAR(25),
[Address] VARCHAR(25),
City VARCHAR(25)
)
INSERT INTO @Employee VALUES
('AA', 'address1', 'City1')
,('AA', 'address1', 'City1')
,('AA', 'address3', 'City1')
,('AA', 'address8', 'City2')
,('BB', 'address5', 'City2')
,('BB', 'address2', 'City1')
,('CC', 'address6', 'City1')
,('CC', 'address7', 'City2')
;WITH cte AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY FullName, [Address], [City] ORDER BY Fullname) AS sl,
HashBytes('MD5', FullName + [Address] + [City]) AS RecordId
FROM @Employee AS e
)
SELECT c.FullName,
c.[Address],
c.City
FROM cte AS c
INNER JOIN cte AS c1
ON c.RecordId = c1.RecordId
WHERE c.sl = 2
Result:
FullName Address City
AA address1 City1
AA address1 City1
source
share