Post reply

Finding duplicate row values

  • December 14, 2001 at 9:07 am

    #101053

    I've been struggling with this for a while. I imagine the answer requires a few temporary tables, but does anyone else have a more elegant solution?

    I have a table with four fields in it. The fourth field contains duplicate values. I want to insert into a new table just the values in the fourth field that have a duplicate value (and ignore values that appear just once).

    For instance, if my source table is like this:

    data1, data2, data3, data4

    data1, data5, data6, data7

    data2, data6, data1, data4

    data3, data3, data5, data9

    data2, data5, data3, data7

    The new table will look like this:

    data4

    data7

    Any brilliant ideas out there?

  • December 14, 2001 at 9:53 am

    #421124

    Not really brilliant, but this may work:

    create table MyTest

    ( MyChar varchar( 10)

    , MyChar2 varchar( 10)

    , MyChar3 varchar( 10)

    , MyChar4 varchar( 10)

    )

    go

    insert MyTest select 'data1', 'data2', 'data3', 'data4'

    insert MyTest select 'data1', 'data5', 'data6', 'data7'

    insert MyTest select 'data2', 'data6', 'data1', 'data4'

    insert MyTest select 'data3', 'data3', 'data5', 'data9'

    insert MyTest select 'data2', 'data5', 'data3', 'data7'

    go

    select * from MyTest

    go

    select MyChar4

    from MyTest

    group by MyChar4

    having count(*) > 1

    go

    drop table MyTest

    Steve Jones

    steve@dkranch.net

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 3, 2002 at 6:06 am

    #421641

    A very simple solution that works perfectly! In my opinion, that makes it brilliant!

    Many thanks for your help Steve.

  • January 3, 2002 at 9:45 am

    #421655

    You're welcome and hope it helps.

    Steve Jones

    steve@dkranch.net

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 23, 2010 at 4:53 am

    #1107731

    br_beneficiarysurname br_dob br_servicepointcode br_beneficiaryid br_dateofregistration br_visitdate row

    G 8/27/1944 SP3139010 T0301390007958300242 8/27/2009 8/27/2009 2

    G 8/27/1944 SP3139010 T0301390007958300240 8/27/2009 8/27/2009 3

    iam getting all duplicate using this query but i didi not got this only two duplicates but there are three duplicates.

    with Duplicaterecords as

    (

    select br_beneficiarysurname,br_dob,br_districtid,br_servicepointcode,br_beneficiaryid,br_dateofregistration,br_visitdate,

    row_number() over(partition by br_districtid,br_dob,br_servicepointcode order by br_districtid,br_dob,br_servicepointcode) as row from TELEMONITORING.beneficiaryregistration

    where br_beneficiarytypeid=3

    )

    select * from Duplicatecalls

    where row>1

    order by br_servicepointcode

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • January 23, 2010 at 5:26 am

    #1107736

    @ malleswarareddy_m:

    Seems like your question is similar to your other thread : So, please don't cross post.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 23, 2010 at 7:05 am

    #1107745

    ok but this is my exact query and i do not cross apply.because this post is some what matching my requirement but not full fledged.

    the above post is my query.

    because i ugently needed this report.by the above query i got duplicate there are 3 duplcate but i will get only 2 duplicate by the above query.

    Thanks

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • January 23, 2010 at 7:07 am

    #1107747

    You have a thread for your question already, don't hijack other people's threads.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 23, 2010 at 7:11 am

    #1107749

    ok i did not hijack others thraed

    but this is my urgent requirement report in company.

    so i posted here understanding me

    ok sorry.

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • January 23, 2010 at 11:23 am

    #1107781

    malleswarareddy_m (1/23/2010)

    ok i did not hijack others thraed

    but this is my urgent requirement report in company.

    so i posted here understanding me

    ok sorry.

    As a side bar, any time something is "urgent", take the time to help others help you. Do like I said on the other thread and read/heed the following article. People will jump through flaming hoops for you if you post data in a readily consumable format like the article suggests.

    http://qa.sqlservercentral.com/articles/Best+Practices/61537/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 10 posts - 1 through 9 (of 9 total)

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