SQL Server Performance

Duplication

Discussion in 'SQL Server 2005 General Developer Questions' started by Jeena, Nov 2, 2007.

  1. Jeena New Member

    Hi all,
    Im having 12 tables in a database. There are duplications inside the tables,also outside the tables. One record in one table may be duplicated in other table in the database.In this situation which query can i give.Pls help.
    Jeena, Nov 2, 2007
    #2

  2. Madhivanan Moderator

    Can you post some sample data with expected result?
    Madhivanan, Nov 2, 2007
    #3

  3. Jeena New Member

    Hi,
    The duplication is for my mobileno.Some times date and year too be duplicated.
    In the same day itself one can send has many sms as they want. But i want a record in which any one of there message is present.
    I need it in the above format itself.Not the mobileno or message alone.Mobileno must be unique,

    I wil give an eg:
    No Mess Date Year
    1234 Hi 10Aug 2007
    5487 Hi 22Aug 2007
    1234 He 22Aug 2006

    In this case the result shuld be like

    1234 ---- ----- -----
    5487 Hi 22Aug 2007

    Any one record of No 1234.

    Hope u can help me.
    Thanks in advance

    Regards
    Jeen
    Jeena, Nov 2, 2007
    #4

  4. Madhivanan Moderator

    Select mobileno,min(message) as message, min(date) as date from table
    group by mobileno
    Madhivanan, Nov 3, 2007
    #5

  5. Jeena New Member

    The above i had given is a sample data. I have 14 colums.
    2009489 E NULL 0 NULL NULL NULL NULL 5/15/2007 2007 6051 NULL NULL NULL
    2009489 E NULL 0 NULL NULL NULL NULL 5/19/2007 2007 6051 NULL NULL NULL
    2009489 E NULL 0 NULL NULL NULL NULL 5/2/2007 2007 6051 NULL NULL NULL
    2009489 E NULL 0 NULL NULL NULL NULL 5/20/2007 2007 6051 NULL NULL NULL
    2009489 Hi NULL 0 NULL NULL NULL NULL 5/3/2007 2007 6051 NULL NULL NULL
    2009489 E NULL 0 NULL NULL NULL NULL 5/4/2007 2007 6051 NULL NULL NULL
    2009489 E NULL 0 NULL NULL NULL NULL 5/6/2007 2007 6051 NULL NULL NULL
    2009489 E NULL 0 NULL NULL NULL NULL 5/7/2007 2007 6051 NULL NULL NULL
    2009489 Hello NULL 0 NULL NULL NULL NULL 5/9/2007 2007 6051 NULL NULL NULL
    The above is my original data. Here only date,message is different.wen i give distinct *all these records comes.But i want only one record from this that gives unique mobileno.While selecting i should get in this same format.
    Thank U in Advance.
    Jeen
    Jeena, Nov 3, 2007
    #6

  6. Madhivanan Moderator

    You need to extend the query I suggested
    Select mobileno,min(col2) as col2, min(col3) as col3, min(col4) as col4..... min(col14) as col14 from table group by mobileno
    Madhivanan, Nov 3, 2007
    #7

  7. Jeena New Member

    Thanks a lot...[:)]
    Just one more doubt.If i have the same mobileno in another table in the same databse.Which query can i give??
    Thanks
    Jeen
    Jeena, Nov 3, 2007
    #8

  8. Madhivanan Moderator

    <P mce_keep="true">[quote user="Jeena"] <P>Thanks a lot...<IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"></P><P>Just one more doubt.If i have the same mobileno in another table in the same databse.Which query can i give??</P><P>Thanks</P><P>Jeen</P><P>[/quote]</P><P>You need to join the above query with table2 matching mobileno</P>
    Madhivanan, Nov 3, 2007
    #9

  9. Jeena New Member

    Hi.. In the above query if i want to delete the duplicated mobileno from other table,can i give delete instead of select?
    Which join should i give?Inner,Outer,Left ....
    Thank u.
    Jeen
    Jeena, Nov 3, 2007
    #10

  10. satya Moderator

    You can also sort and select eliminate duplicates. But that only eliminates dups from your input data. To only add rows that don't already exist, the lookup is the way as you described.
    Also blogged about this as well:
    http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/21/9368.aspx kubj,
    satya, Nov 3, 2007
    #11

  11. Jeena New Member

    Hi..
    Im not going to insert a single value into that tables. All that i want to do is to eliminate duplicate records from other tables.A value(in this case mobileno) in one table should not be in the other.
    Thanks In advance
    Jeen
    Jeena, Nov 3, 2007
    #12

  12. Adriaan New Member

    I'm having problems trying to understand a requirement where a key value may only occur in one table, and at the same time the key is in use for a number of tables. Could be poor normalization, or the lack of a "status" type of column.
    Other than that, sounds like you need some IF EXISTS checks in insert/update triggers.
    Adriaan, Nov 5, 2007
    #13

Share This Page