Remove duplicates of Data based on date by same ID

Question

SELECT 
    ,[ID]
    ,[Name]
    ,[Age]
    ,[CheckTime]
FROM Record

What I have now

ID	Name	Age	CheckTime
12	Alex	23	2022-08-16 06:16:46.000
13	Cynthia	45	2022-08-16 06:16:53.000
14	Kwabeng	57	2022-08-16 07:54:44.000
14	Kwabeng	57	2022-08-16 07:54:51.000
15	Asante	23	2022-08-16 07:54:32.000
16	Leticia	98	2022-08-16 07:58:32.000
16	Leticia	98	2022-08-16 07:45:49.000
12	Mercy	23	2022-08-16 07:42:36.000

From the table id number 14 and 16 have been repeated but just that the Date is different, I want to remove one of the record

What I want

ID	Name	Age	CheckTime
12	Alex	23	2022-08-16 06:16:46.000
13	Cynthia	45	2022-08-16 06:16:53.000
14	Kwabeng	57	2022-08-16 07:54:44.000
15	Asante	23	2022-08-16 07:54:32.000
16	Leticia	98	2022-08-16 07:58:32.000
12	Mercy	23	2022-08-16 07:42:36.000

I was able to achieve that with FORMAT(CheckTime,'yyyy-MM-dd:HH') but it has converted the CheckTime to string but I want the CheckTime in datetime format to help in filtering records by date

SELECT ID, Name, Age, FORMAT(CheckTime, 'yyyy-MM-dd:HH') AS [DateHour]
    , COUNT(Name) AS [Age]
FROM Record
GROUP BY ID,Name, Age, FORMAT(CheckTime, 'yyyy-MM-dd:HH'),

Why are there 2 rows for ID 12 in your expected results? What's special about Mercy and Alex? — Thom A, Aug 17 '22 at 07:39

score 1 · Answer 1 · answered Aug 17 '22 at 04:21

Assuming that you want to retain the earliest record from a pair of duplicates, we can try using a deletable CTE with the help of ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CheckTime) rn
    FROM Record
)

DELETE
FROM cte
WHERE rn > 1;

If you instead just want to view your data this, then use:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CheckTime) rn
    FROM Record
)

SELECT ID, Name, Age, CheckTime
FROM cte
WHERE rn = 1;

score 0 · Answer 2 · answered Aug 17 '22 at 04:34

Please try this method, I trying to show with one example.

We can use the SQL RANK function to remove the duplicate rows as well. SQL RANK function gives unique row ID for each row irrespective of the duplicate row.

In the following query, we use a RANK function with the PARTITION BY clause. The PARTITION BY clause prepares a subset of data for the specified columns and gives rank for that partition.

SELECT E.ID, 
    E.firstname, 
    E.lastname, 
    E.country, 
    T.rank
FROM [SampleDB].[dbo].[Employee] E
  INNER JOIN
(
 SELECT *, 
        RANK() OVER(PARTITION BY firstname, 
                                 lastname, 
                                 country
        ORDER BY id) rank
 FROM [SampleDB].[dbo].[Employee]
) T ON E.ID = t.ID;

In the screenshot, you can note that we need to remove the row having a Rank greater than one. Let’s remove those rows using the following query.

DELETE E
    FROM [SampleDB].[dbo].[Employee] E
         INNER JOIN
    (
        SELECT *, 
               RANK() OVER(PARTITION BY firstname, 
                                        lastname, 
                                        country
               ORDER BY id) rank
        FROM [SampleDB].[dbo].[Employee]
    ) T ON E.ID = t.ID
    WHERE rank > 1;

Thanks

You should be partitioning based on the unique `ID` field. But even if there were first name, last name, and country columns (there are not), this might not be sufficient to uniquely identify each person. Even in the US, for example, there are 2 people with the same "Tim Biegeleisen." — Tim Biegeleisen, Aug 17 '22 at 04:37
Yes, We can. Thanks for suggestion. I will check this into my SSMS. — Jack D, Aug 17 '22 at 04:48

Remove duplicates of Data based on date by same ID

2 Answers2