locked
How to count/display duplicate records RRS feed

  • Question

  • Hi, I’ve a table like this

    Id       IP       DOB

    1        1.1.1.1          1/1/2000

    2        2.2.2.2          2/2/2001

    3        1.1.1.1          1/1/2000

    4        1.1.1.1          3/3/2000

    5        2.2.2.2          4/4/2001

    6        1.1.1.1          2/1/2000

    I need to display duplicate records

    I need the following output

    Id       IP       DOB

    1        1.1.1.1          1/1/2000

    3        1.1.1.1          1/1/2000

    If 2 users have same IP & DOB, I want to display 2nd record as duplicate record

    Please advice, How?


    • Edited by gvj Thursday, September 13, 2012 9:11 PM
    Thursday, September 13, 2012 8:47 PM

Answers

  • Try

    with data as (
    select convert(datetime,'20120101') as dob, '1.1.1.1' AS IP, 1 as ID
    union
    select convert(datetime,'20120102') as dob, '1.1.1.2' AS IP, 2 as ID
    union
    select convert(datetime,'20120102') as dob, '1.1.1.2' AS IP, 3 as ID
    union
    select convert(datetime,'20120101') as dob, '1.1.1.1' AS IP, 4 as ID
    union
    select convert(datetime,'20120102') as dob, '1.1.1.2' AS IP, 5 as ID
    union
    select convert(datetime,'20120103') as dob, '1.1.1.3' AS IP, 6 as ID
    )
    
    select data.*, case when ROW_NUMBER() over (PARTITION BY IP, DOB ORDER BY Id) > 1 then 'Duplicate' else '' end as DuplicateIndicator
    from data  
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by JR1811 Friday, September 14, 2012 1:35 AM
    • Marked as answer by Iric Wen Monday, September 24, 2012 9:41 AM
    Thursday, September 13, 2012 11:16 PM

All replies

  • No probs.  First get a list of the first record for each client. Grouping and using MIN for Record ID.

    Then left join onto this from the whoe data set to find those which AREN'T the first record for each user:

    with data as (
    select convert(datetime,'20120101') as dob, '1.1.1.1' AS IP, 1 as ID
    union
    select convert(datetime,'20120102') as dob, '1.1.1.2' AS IP, 2 as ID
    union
    select convert(datetime,'20120102') as dob, '1.1.1.2' AS IP, 3 as ID
    union
    select convert(datetime,'20120101') as dob, '1.1.1.1' AS IP, 4 as ID
    union
    select convert(datetime,'20120102') as dob, '1.1.1.2' AS IP, 5 as ID
    union
    select convert(datetime,'20120103') as dob, '1.1.1.3' AS IP, 6 as ID
    )
    ,
    firstRec AS 
    (
    select
    	dob, IP, MIN(id) AS Record
    from
    	data
    group by
    	dob, ip
    )
    
    select
    	data.*
    from
    	data
    left join
    	firstRec on firstrec.Record = data.ID
    where
    	firstrec.record is null
    	

    Regards
    James

    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Thursday, September 13, 2012 9:02 PM
  • Try this -

    DECLARE @duprecords TABLE(id int,ip varchar(20),dob date)
    INSERT INTO @duprecords
    SELECT 1,'1.1.1.1','1/1/2000' UNION ALL
    SELECT 2,'2.2.2.2','2/2/2001' UNION ALL
    SELECT 3,'1.1.1.1','1/1/2000' UNION ALL
    SELECT 4,'2.2.2.2','3/3/2000' UNION ALL
    SELECT 5,'2.2.2.2','4/4/2001' UNION ALL
    SELECT 6,'1.1.1.1','2/1/2000' 
    SELECT * FROM @duprecords
    ;WITH cte1 AS
    (
    SELECT ip,dob FROM @duprecords
    group by ip,dob 
    HAVING count(*)>1
    )
    SELECT a.id,a.ip,a.dob FROM @duprecords a JOIN cte1
    ON cte1.ip=a.ip
    AND cte1.dob=a.dob

    Output -

    id	ip	dob
    1	1.1.1.1	2000-01-01
    3	1.1.1.1	2000-01-01


    If you want the dob to be displayed as input : using Convert function

    ;WITH cte1 AS
    (
    SELECT ip,dob FROM @duprecords
    group by ip,dob 
    HAVING count(*)>1
    )
    SELECT a.id,a.ip,convert(varchar, a.dob, 103)FROM @duprecords a JOIN cte1
    ON cte1.ip=a.ip
    AND cte1.dob=a.dob

    • Edited by Naarasimha Thursday, September 13, 2012 9:07 PM
    Thursday, September 13, 2012 9:03 PM
  • TRY : there might be other ways ! i just thought of this !

    create  table #tempdup
    (Id  int,IP varchar(10) ,DOB date) 
    insert into #tempdup values (1,'1.1.1.1','1/1/2000')
    insert into #tempdup values (2,'2.2.2.2','2/2/2001')
    insert into #tempdup values (3,'1.1.1.1','1/1/2000')
    insert into #tempdup values (4,'1.1.1.1','3/3/2000')
    insert into #tempdup values (5,'2.2.2.2','4/4/2001')
    insert into #tempdup values (6,'1.1.1.1','2/1/2000')



    WITH    dup
              AS ( SELECT   ID,ip,dob
                          , row_number() OVER ( PARTITION BY ip,dob ORDER BY iP ) AS nr
                   FROM     #tempdup
                 )
        sELECT A.ID,A.IP,A.DOB FROM #tempdup A
        INNER JOIN DUP B ON A.IP = B.IP AND A.DOB = B.DOB
        WHERE   nr > 1 

    output :

    ID IP DOB
    1 1.1.1.1 2000-01-01
    3 1.1.1.1 2000-01-01

    • Edited by JR1811 Thursday, September 13, 2012 9:04 PM
    Thursday, September 13, 2012 9:03 PM
  • try like this :-

    select * from tablename

    where ID  in (select min(id) from tablename group by IP having count(*) >1)


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, September 13, 2012 9:08 PM
  • Try

    with data as (
    select convert(datetime,'20120101') as dob, '1.1.1.1' AS IP, 1 as ID
    union
    select convert(datetime,'20120102') as dob, '1.1.1.2' AS IP, 2 as ID
    union
    select convert(datetime,'20120102') as dob, '1.1.1.2' AS IP, 3 as ID
    union
    select convert(datetime,'20120101') as dob, '1.1.1.1' AS IP, 4 as ID
    union
    select convert(datetime,'20120102') as dob, '1.1.1.2' AS IP, 5 as ID
    union
    select convert(datetime,'20120103') as dob, '1.1.1.3' AS IP, 6 as ID
    )
    
    select data.*, case when ROW_NUMBER() over (PARTITION BY IP, DOB ORDER BY Id) > 1 then 'Duplicate' else '' end as DuplicateIndicator
    from data  
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by JR1811 Friday, September 14, 2012 1:35 AM
    • Marked as answer by Iric Wen Monday, September 24, 2012 9:41 AM
    Thursday, September 13, 2012 11:16 PM