Remove data row seems to be duplicate where one column not same

  • Following is Sample data of a table, I have to write a view where I need to exclude the row which seems to be duplicate row with col2 having value 'Dup1' but col6 is not same for the row.

    Can some one tell me what select query would be that only row 6 is picked and row 5 shd not.

    Col1Col2 Col3Col4Col5Col6 Col7

    16170964Test A25907636149820100207NULL

    26173577Test M32081144499820100207NULL

    36172998Test C25168935399820100207NULL

    46172230Test D38564948999820100207NULL

    56152082Dup1999995313482010010651459133

    66152082Dup1999995313482010010651466385

    76164820Test Y4026326417982010021051729991

    86173037Test J6222467301982010012651822310

    96173748Test K2740483766982010012751822397

    106173016Test M2982544012732010012751822397

    116165180Test N2911383940482010020151823044

    126174486Test L3562464601482010020151823044

    Thanks in advance.

    /**A strong positive mental attitude will create more miracles than any wonder drug**/

  • Dupe = ROW_NUMBER() OVER(PARTITION BY Col1 Col2 Col3 Col4 Col5 Col6 ORDER BY Col1 Col2 Col3 Col4 Col5 Col6 Col7)

    If you set up your sample as a CREATE TABLE statement with appropriate INSERTs for your data, then folks can test against it straight way, without spending 10 mins turning your data into a table.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Thank you that served the purpose. I'll provide the Insert scripts in future, thanks for it.

    /**A strong positive mental attitude will create more miracles than any wonder drug**/

  • ChrisM@home (2/12/2010)


    Dupe = ROW_NUMBER() OVER(PARTITION BY Col1 Col2 Col3 Col4 Col5 Col6 ORDER BY Col1 Col2 Col3 Col4 Col5 Col6 Col7)

    If you set up your sample as a CREATE TABLE statement with appropriate INSERTs for your data, then folks can test against it straight way, without spending 10 mins turning your data into a table.

    Thank you Chris for this suggestion. Much more elegant than the other solutions I have seen on this question, and it does not require the creation of a temp table to eliminate duplicates.

  • You can also just use a grouping function in a derived table:

    select sq.col1, t.col2, t.col3, t.col4, t.col5, t.col6, t.col7

    from

    (select MIN(col1) as col1

    from #test

    group by col2, col3, col4, col5, col6) sq

    join #test t

    on t.col1 = sq.col1

    order by sq.col1

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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