duplicate rows

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » duplicate rows

duplicate rows

Rate Topic

Display Mode

Topic Options

Author

Message

harri.reddy

Posted Sunday, November 11, 2012 5:08 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346

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

Post #1383484

Jeff Moden

Posted Sunday, November 11, 2012 11:03 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

harri.reddy (11/11/2012)

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1383502

vinu512

Posted Monday, November 12, 2012 12:25 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:37 AM
Points: 1,047, Visits: 1,439

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 Wink

Post #1383517

Phil Parkin

Posted Monday, November 12, 2012 1:42 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:08 AM
Points: 4,236, Visits: 9,473

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

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

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1383545

Jeff Moden

Posted Monday, November 12, 2012 9:16 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

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! Tongue

--Jeff Moden

Post #1383733

« Prev Topic | Next Topic »

Permissions