how delete duplicate rows from table without primary key

• ashok-831066

  Right there with Babe

  Points: 778

  More actions

  November 25, 2009 at 11:44 pm

  #135604

  Hi,

  I have doubt. I am creating table and insert two rows.

  This is my table .

  BookName Price Author

  C++ 150 kanithkar

  C++ 150 kanithkar

  In the table no primary key. I need to delete duplicate rows without primary key and my output would be like

  BookName Price Author

  C++ 150 kanithkar

  Give a hintto me.Hope your's reply.

  Thanks

• dmoldovan

  SSCertifiable

  Points: 6334

  More actions

  November 26, 2009 at 12:10 am

  #1084464

  See if this helps you:

  http://blogs.techrepublic.com.com/datacenter/?p=420

• only4mithunc

  SSCrazy Eights

  Points: 8434

  More actions

  November 26, 2009 at 9:42 am

  #1084646

  hi use the below query.

  WITH DUPE AS

  (

  SELECT BOOKNAME,PRICE,AUTHOR,ROW_NUMBER() OVER(ORDER BY BOOKNAME,PRICE,AUTHOR ) AS ROWNUMBER

  FROM TABLENMAE )

  DELETE FROM DUPE

  WHERE ROWNUMBER NOT IN (SELECT MAX(ROWNUMBER) FROM DUPE GROUP BY BOOKNMAE,PICE,AUTHOR

  )

  Regards,

  MC

  Thanks & Regards,
  MC

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  November 26, 2009 at 10:51 am

  #1084666

  ashok

  CREATE TABLE #Books(BookName VARCHAR(20), Price MONEY, Author VARCHAR(50))

  INSERT INTO #Books

  SELECT 'C++', 150, 'kanithkar' UNION ALL

  SELECT 'C++', 150, 'kanithkar' UNION ALL

  SELECT 'C#', 150, 'kanithkar' UNION ALL

  SELECT 'Net', 250, 'smith' UNION ALL

  SELECT 'Net', 250, 'jones' UNION ALL

  SELECT 'SQL', 250, 'jack'

  ;with numbered as(SELECT rowno=row_number() over

  (partition by Bookname, Author order by Bookname),Bookname,Author,Price from #Books)

  --For testing use:

  SELECT * FROM numbered --To check if output is correct

  --Once tested and output is correct replace above statement with

  DELETE from numbered WHERE rowno > 1

  My select output:

  rownoBookname AuthorPrice

  1C# kanithkar150.00

  1C++ kanithkar150.00

  2C++ kanithkar150.00

  1Net jones 250.00

  1Net smith 250.00

  1SQL jack 250.00

  Changed T-SQL after testing to use DELETE. Resuls:

  (1 row(s) affected)

  BookName Price Author

  C++ 150.00kanithkar

  C# 150.00kanithkar

  Net 250.00smith

  Net 250.00jones

  SQL 250.00jack

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  November 27, 2009 at 9:02 am

  #1084943

  bitbucket has the better solution.

  also, may I recommend you add a primary key to your table(s)??

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  November 27, 2009 at 11:16 am

  #1084993

  TheSQLGuru

  Thank you for the rcommendation.

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]