Post reply

duplicate rows

  • November 11, 2012 at 5:08 pm

    #264618

    hi

    my table has following data

    productno productname des quantity

    1 borin 4x-mal 2

    1 borin 5x-cal 3

    2 hypoid 4-5cal 4

    2 hypoid 4-5cal 4

    here,i want to delete rows which has productno and productname same,it doesnt matter des and quantity is same or not.

    i just want 1 row ,with combination of productno and productname

  • November 11, 2012 at 11:03 pm

    #1558008

    harri.reddy (11/11/2012)

    hi

    my table has following data

    productno productname des quantity

    1 borin 4x-mal 2

    1 borin 5x-cal 3

    2 hypoid 4-5cal 4

    2 hypoid 4-5cal 4

    here,i want to delete rows which has productno and productname same,it doesnt matter des and quantity is same or not.

    i just want 1 row ,with combination of productno and productname

    Once again, it would help you a lot if you spent an additional minute or two to make some readily consumable data as I've suggested a couple of times now. That way, you'll get a nice tested coded answer instead of someone just saying "use ROW_NUMBER() with a partion on the two columns and delete everything that doesn't show up as a "1"".

    --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)

  • November 12, 2012 at 12:25 am

    #1558019

    This is how you can do it.

    And from next time onwards post some readily consumable sample data.

    This is how you post readily consumable sample data:

    --Creating Table

    Create Table Ex

    (productno int,

    productname NVarchar(20),

    des NVarchar(20),

    quantity int )

    --Inserting Sample Data

    Insert Into Ex Values(1, 'borin', '4x-mal', 2)

    Insert Into Ex Values(1, 'borin', '5x-cal', 3)

    Insert Into Ex Values(2, 'hypoid', '4-5cal', 4)

    Insert Into Ex Values(2, 'hypoid', '4-5cal', 4)

    How hard is it anyway???

    Following is the query for your requirement:

    ;With CTE

    As

    (

    Select *, ROW_NUMBER() Over (Partition By productno, productname Order By Productno) As rn From Ex

    )

    Delete From CTE Where rn = 2

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • November 12, 2012 at 1:42 am

    #1558046

    ...readily consumable data as I've suggested a couple of times now

    "Head banging" and "brick wall" come to mind.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • November 12, 2012 at 9:16 am

    #1558205

    Phil Parkin (11/12/2012)

    ...readily consumable data as I've suggested a couple of times now

    "Head banging" and "brick wall" come to mind.

    Diode, Check Valve, and One-way-street do too! 😛

    --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)

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

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