SQLServer Dup Deletion

-- dup text1 and Id
CREATE TABLE #table1 (
      Id INT
    , text1 VARCHAR(50)
    , dtc DATETIME2(0)
);
INSERT INTO #table1 (
      Id
    , text1
    , dtc
)
VALUES
      (1, 'test1', DATEADD(d, -10, GETDATE()))
    , (1, 'test1', DATEADD(d, -7, GETDATE()))
    , (2, 'test2', DATEADD(d, -7, GETDATE()))
    , (2, 'test2', DATEADD(d, -10, GETDATE()))

SET ROWCOUNT 1
DELETE FROM #table1 WHERE Id = 1
DELETE FROM #table1 WHERE Id = 2
SET ROWCOUNT 0

SELECT * FROM #table1
DROP TABLE #table1

-- dup text1 but different primary key
CREATE TABLE #table2 (
      Id INT
    , text1 VARCHAR(50)
    , dtc DATETIME2(0)
);
INSERT INTO #table2 (
      Id
    , text1
    , dtc
)
VALUES
      (1, 'test1.1', DATEADD(d, -10, GETDATE()))
    , (2, 'test1.2', DATEADD(d, -7, GETDATE()))
    , (3, 'test1.1', DATEADD(d, -7, GETDATE()))
    , (4, 'test1.2', DATEADD(d, -10, GETDATE()))

SELECT text1
FROM #table2
GROUP BY text1
HAVING COUNT(*) > 1

SELECT * FROM #table2

DELETE FROM #table2
WHERE Id IN(
    SELECT DISTINCT m1.Id
    FROM #table2 m1
            LEFT JOIN #table2 m2 ON m1.text1 = m2.text1 AND m1.dtc < m2.dtc
    WHERE m2.Id IS NOT NULL
)

SELECT * FROM #table2
DROP TABLE #table2

-- Dup record with same key, keep distinct data, this one is a problem if you need to maintain the PK
CREATE TABLE #table3 (
      Id INT
    , text1 VARCHAR(50)
    , dtc DATETIME2(0)
);
INSERT INTO #table3 (
      Id
    , text1
    , dtc
)
VALUES
      (1, 'test1.1', DATEADD(d, -10, GETDATE()))
    , (1, 'test1.2', DATEADD(d, -7, GETDATE()))
    , (2, 'test2.1', DATEADD(d, -10, GETDATE()))
    , (2, 'test2.2', DATEADD(d, -7, GETDATE()))

SELECT * FROM #table3

SELECT DISTINCT text1, dtc
INTO #holdingTable
FROM #table3

TRUNCATE TABLE #table3

INSERT INTO #table3 (Id, text1, dtc)
SELECT ROW_NUMBER() OVER(ORDER BY text1, dtc), text1, dtc
FROM #holdingTable

SELECT * FROM #table3

DROP TABLE #table3
DROP TABLE #holdingTable

-- dup records with different keys but this can consolidate information
CREATE TABLE #table4(
      Id INT
    , firstName VARCHAR(20)
    , lastName VARCHAR(20)
    , email VARCHAR(50)
    , phone VARCHAR(10)
    , dateOfBirth DATE
    , rating INT
)

INSERT INTO #table4 (Id, firstName, lastName, email, phone, dateOfBirth, rating)
VALUES
      (1, 'Jack', 'Smith', NULL, NULL, DATEADD(yy, -30, GETDATE()), 5)
    , (2, 'Jack', 'Smith', '[email protected]', NULL, NULL, 6)
    , (3, 'Jack', 'Smith', NULL, '2145556666', NULL, 2)
    , (4, 'Jill', 'Jones', NULL, NULL, DATEADD(yy, -25, GETDATE()), 1)
    , (5, 'Jill', 'Jones', '[email protected]', NULL, NULL, 10)
    , (6, 'Jill', 'Jones', NULL, '2145557777', NULL, 0)
    , (7, 'Jimmy', 'Jackson', NULL, '2146668877', DATEADD(yy, -45, GETDATE()), 6)
    , (8, 'Jimmy', 'Jackson', '[email protected]', NULL, NULL, 6)
    , (9, 'Jimmy', 'Jackson', '[email protected]', '2145558888', DATEADD(yy, -15, GETDATE()), 5);

SELECT * FROM #table4;

WITH cte AS (
    SELECT
          Id
        , firstName
        , lastName
        , MAX(email) OVER (PARTITION BY firstName, lastName) AS emailUpdated
        , MAX(phone) OVER (PARTITION BY firstName, lastName) AS phoneUpdated
        , MAX(dateOfBirth) OVER (PARTITION BY firstName, lastName) AS dateOfBirthUpdated
        , ROW_NUMBER() OVER (PARTITION BY firstName, lastName ORDER BY firstName, lastName) AS Rn
        , MIN(rating) OVER (PARTITION BY firstName, lastName) AS ratingUpdated
    FROM #table4
)
SELECT
      Id
    , firstName
    , lastName
    , emailUpdated
    , phoneUpdated
    , dateOfBirthUpdated
    , ratingUpdated
    , Rn
INTO #noDups
FROM cte
WHERE Rn = 1

SELECT *
FROM #noDups

DROP TABLE #table4
DROP TABLE #noDups