CODEWITHSUNDEEP
sqlsql-server
hikizume
hikizume

Reputation: 528

SQL query get newly duplicated rows

Let's say I have this table structure

User
====
*Id
*Email
*City
*DateCreated

with this data:

Id Email                           City         DateCreated
1  [email protected]                Hawkins      01-01-2018
2  [email protected]            Hawkins      01-01-2018
3  [email protected]          Hawkins      01-01-2018
4  [email protected]      Hawkins      01-01-2018
5  [email protected]                Hawkins      01-01-2021
6  [email protected]                Pittsburgh   01-01-2021

I want to run a query which returns only the latest duplicate record (same email and city):

5  [email protected]                Hawkins      01-01-2021

Upvotes: 1

Views: 59

Answers (3)

Charlieface
Charlieface

Reputation: 71927

You can use window functions to get this result

SELECT *
FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY t.Email, t.City ORDER BY t.DateCreated DESC) rn,
        COUNT(*)     OVER (PARTITION BY t.Email, t.City) cnt
    FROM yourTable t
) t
WHERE t.cnt > 1 AND t.rn = 1

Remove the COUNT and its filter if you want also rows which have no duplicates at all.

Upvotes: 3

MertG
MertG

Reputation: 763

You can do it using window functions.

;WITH FilteredResult AS (
    SELECT 
        Id,
        Email,
        City,
        DateCreated,
        DuplicatedRows  = ROW_NUMBER() OVER(PARTITION BY Email, City ORDER BY Id)
    FROM your_table_name
)
SELECT *
FROM FilteredResult
WHERE DuplicatedRows = 1

Upvotes: 1

Bianca Rodriguez
Bianca Rodriguez

Reputation: 65

In this case, the record shown is not a duplicate because the city differs from ID 5 and ID 6.

But you could probably do row_number function and partition by email and date created.

Here's a good example https://www.c-sharpcorner.com/blogs/rownumber-function-with-partition-by-clause-in-sql-server1

Upvotes: 0

Related Questions