Duplicate Rows in certain time

  • ferby

    SSC Enthusiast

    Points: 128

    Hello,

    i had this problem allready in a normal SQL Server Version and solve it with CTEs or Cursors.

    Now I have to Use Compact SQL .NET 3.5 and i have no idea how to solve it:

    If have

    Attribute 1

    Attribute 2

    Attribute x

    Timestamp y

    If a row have all attributes in common an a timestamp with less then 60 seconds differenze then only the first record should be taken and the other ones delete.

    The Table has now Key!

    For Example

    A, B, C, 02.09.2013 16:06:30

    A, B, C, 02.09.2013 16:06:35

    A, B, C, 02.09.2013 16:06:30

    A, B, X, 02.09.2013 16:06:30

    A, B, C, 02.09.2013 16:06:20

    A, B, Y, 02.09.2013 16:06:30

    Output should be:

    A, B, X, 02.09.2013 16:06:30

    A, B, C, 02.09.2013 16:06:20

    A, B, Y, 02.09.2013 16:06:30

    Have someone an idea?

    Thx for helping.

  • Abu Dina

    SSChampion

    Points: 14155

    My attempt:

    create table test (Attribute1 char(1), attribute2 char(1), attribute3 char(1), thetimestamp datetime)

    insert into test(Attribute1, attribute2, attribute3, thetimestamp)

    select 'A', 'B', 'C', '2013-09-02 16:06:30' union all

    select 'A', 'B', 'C', '2013-09-02 16:06:35' union all

    select 'A', 'B', 'C', '2013-09-02 16:06:30' union all

    select 'A', 'B', 'X', '2013-09-02 16:06:30' union all

    select 'A', 'B', 'C', '2013-09-02 16:06:20' union all

    select 'A', 'B', 'Y', '2013-09-02 16:06:30' union all

    select 'A', 'B', 'C', '2013-09-02 16:10:00'

    select c.Attribute1, c.attribute2, c.attribute3, c.thetimestamp

    from test as a

    cross apply (select b.Attribute1, b.attribute2, b.attribute3, MIN(thetimestamp) as thetimestamp

    from test as b

    where a.Attribute1 = b.Attribute1

    and a.attribute2 = b.attribute2

    and a.attribute3 = b.attribute3

    and datediff(ss, b.thetimestamp, a.thetimestamp ) <= 60

    group by b.Attribute1, b.attribute2, b.attribute3) as c

    group by c.Attribute1, c.attribute2, c.attribute3, c.thetimestamp

    select * from test

    drop table test

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • ferby

    SSC Enthusiast

    Points: 128

    BIG THX, IT WORKS!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply