Deleting duplicates rows

  • hai friends,

    i m created one table ,i wanna delete the duplicate values

    create table empl

    (

    row_id int,

    ename varchar(120),

    job varchar(120),

    sal varchar(100)

    )

    insert into empl ('4','ram','IT','60000')

    insert into empl ('4','ram','IT','60000')

    insert into empl ('4','ram','IT','60000')

    insert into empl ('4','ram','IT','60000')

    i am write the query to delete duplicates

    delete from empl t

    where t.ename>(select min(t1.ename) from empl t2 where t.job=t2.job and t.sal=t2.sal)

    but its was showimg error "Incorrect syntax near 't'." like dis do the need full

  • This should help you out

    ; WITH cte_Delete_Dups AS

    (

    SELECTROW_NUMBER() OVER ( PARTITION BY row_id, ename, job, sal ORDER BY row_id ) AS RN, *

    FROMempl

    )

    DELETE

    FROMcte_Delete_Dups

    WHERErn > 1


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • DROP table #empl

    CREATE TABLE #empl

    (

    row_id int,

    ename varchar(120),

    job varchar(120),

    sal varchar(100)

    )

    INSERT #empl VALUES

    ('4','ram','IT','60000'),

    ('4','ram','IT','60000'),

    ('4','ram','IT','60000'),

    ('4','ram','IT','60000');

    WITH OrderedData AS (

    SELECT row_id, ename, job, sal,

    rn = ROW_NUMBER() OVER(PARTITION BY row_id, ename, job, sal ORDER BY row_id, ename, job, sal)

    FROM #empl

    ) DELETE OrderedData WHERE rn > 1

    SELECT * FROM #empl

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

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