duplicate rows

Question

duplicate rows

harri.reddy

SSCommitted

Points: 1957
More actions
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

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

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

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)

vinu512 SSCoach Points: 15729 More actions · Answer 2

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)

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] 😉

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

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

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

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)