How to Compare Rows in SQL?

SQL SERVER – Delete Duplicate Records – Rows March 1, 2007 by pinaldave Following code is useful to delete dup?

• SQL SERVER – Delete Duplicate Records – Rows I have on table named [Duplicate] {ID int,FNAME varchar(10),MNAME varchar(10)} Here there is no key and here are duplicate rows. so hoca can i delete this duplicate rows. Check Data ID FNAME LNAME 1 AAA CCC 2 BBB DDD 1 AAA CCC 2 BBB DDD 1 AAA CCC 2 BBB DDD 3 BCB DGD Remove duplicate rows and keep the data in to the table like this using single query. ID FNAME LNAME 1 AAA CCC 2 BBB DDD 3 BCB DGD PLease if possible help because i faced this question in many interviews. Reply me

• Answer:

  The tricky part about duplicate elimination is retaining just one of the duplicates when there isn't a way to differentiate between them. One of the less error-prone way to do so is to make it so you CAN tell them apart: add a column to the table and make it autonumber or something similar. Then, you can create a simple procedure along the lines of CREATE CURSOR delete_targets AS SELECT id, fname, lname, MIN(newField) AS keepValue FROM someTable GROUP BY id, fname, lname HAVING COUNT(*) > 1 Then, fetch each row of the delete_targets into @id, @fname, @lname and @keepValue and then: DELETE FROM someTable WHERE id = @id AND fname = @fname AND lname = @lname AND newField > @keepValue After you're done, drop the new column.