eliminating duplicates

  • Hello All,

    i have a table with dupicate records.

    I wish to delete the first row among the duplicate records and keep the others :hehe: and the other way round.

    How should have right the DELETE statement with top 1 clause in it. :crazy:

    Thanks in Advance..!!

  • Hi vish

    u have not mentioned which version of sql server u r using because if u are using sql server 2000 then u will not be able to use Delete top clause.

  • Simply you can use DISTINCT keyword.

    If you give some sample data's you will get lot of help from here.

    karthik

  • Say for example,

    create table #Emp

    (

    Eno int,

    Ename varchar(5)

    )

    insert into #Emp

    select 1,'AA'

    union all

    select 1,'AA'

    union all

    select 2,'BB'

    union all

    select 3,'CC'

    select distinct * into #FinalResult

    from #Emp

    will give you the expected result.

    karthik

  • How you do this depends on a couple of factors. How many rows are in your table? How many rows are dupes that need to be deleted?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Hi All,

    Let me give my problem description in detail.

    I use SQL server 2000.

    I have a table 'TAB' with 3 coulumns 'Name','Age','Sex'

    and there is no primary key.

    I have multiple duplicate data in my table. for eg..

    ABC24M

    ABC24M

    LMN27M

    LMN27M

    LMN27M

    PQRS25F

    XYZ24M

    XYZ25M

    Now i would wish to 'DELETE' the 1st original row ( Row no 1,3,6,7)

    and keep the other duplcates from above data. 😎

    Now, If there is a row without any dupicates present.. it will be deleted ( for eg Row no 6)

    The condition is i dont want to go for intermediate tables or have any

    additional identity column. :w00t:

    Please help..!!

    Thanks folks

  • vish,

    I don't see any numbers assigned to the rows.

    How can I tell which ones are 1,3,6,7?

    _____________
    Code for TallyGenerator

  • Hi,

    There no row numbers associated.. i just wanted to make it clear..

    through that example.

    Thanks

  • vish (6/19/2008)i just wanted to make it clear..

    You did not succeed.

    For me these lines:

    LMN 27 M

    LMN 27 M

    LMN 27 M

    look absolutely identical.

    If it's all you've go in table they are identical for SQL Server as well.

    Open this table in EM and try to remove one of the rows.

    I will not do because of "not enough key information".

    If you try to delete it from QA you need to specify which row(s) to delete:

    DELETE

    FROM Table

    WHERE ... = 'LMN' AND ... = 27 AND ''' = 'M'

    AND ?????

    Without specifying what do you mean by ????? all 3 rows will be deleted.

    So, you need to define the criteria for the deletion more specifically.

    _____________
    Code for TallyGenerator

  • hi sergiy,

    Thats right..

    Those rows are practically identical...

    So the point is we need to have some condition which will distinguish them from each other...

    So i feel its quite impossible under such given conditions.

    I think i get the blackhole.

    Thanks to all for your responses..!!

    vish 😎

  • Hi,

    U can use RowID property to delete the duplicate records, I think.

  • Can you explain what the point to have 3 identiacl rows?

    And especially what's the point of keeping 2 of 3 identiacal rows?

    Does it make any difference which 1 of 3 rows to delete if the all are IDENTICAL?

    _____________
    Code for TallyGenerator

  • Although you have stated you do not wish to go through intermediate tables, the most suitable solution for you would be to select distinct rows from TAB into a TEMP table, delete all from TAB, and insert the records from TEMP back into TAB. Then make sure you have placed enough checks and constraints to ensure no duplicates creep in again. A simple UNIQUE index on a combination of key columns will suffice.


    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • alter table add ids int identity

    delete MN

    from MN

    Inner Join (select ) AL

    ON AL. AL.ids

    alter table drop column ids

  • The condition is i dont want to go for intermediate tables or have any

    additional identity column

    Who has placed these conditions on your solution? The only way I can see you having to even attempt this with those conditions in place is if this is a homework assignmnet.

    There are some quite simple methods to do what you are asking. Do you still want help with a solution?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 15 posts - 1 through 15 (of 137 total)

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