Display Duplicate Rows Only once
-
Hi All, I have a table with the below columns. ID , First Name , Last Name , City. The ID column is unique. This table contains duplicate rows (First Name , Last Name , City values are same). Could you please advise me on how to write a select query to display duplicate rows only once along with the other rows. Thanks and Regards Pha
-
Hi All, I have a table with the below columns. ID , First Name , Last Name , City. The ID column is unique. This table contains duplicate rows (First Name , Last Name , City values are same). Could you please advise me on how to write a select query to display duplicate rows only once along with the other rows. Thanks and Regards Pha
Do you need the ID for display? If not, a simple DISTINCT might be easiest.
-
Do you need the ID for display? If not, a simple DISTINCT might be easiest.
Thanks for the response. But i need the ID as well in the display. Regards Pha
-
Do you need the ID for display? If not, a simple DISTINCT might be easiest.
-- ALWAYS TEST FIRST -- I don't have your table name -- REPLACE "TABLE" WITH THE ACTUAL TABLE NAME -- Try this to select the dups select count(*),FirstName , LastName , City from "TABLE" group by FirstName , LastName , City having count(*) > 1 -- select the data into another table and TEST THIS -- REPLACE "TABLE" WITH THE ACTUAL TABLE NAME --*********************** DELETE DUPS *************************************** -- the Magic is in the rowcount setting!!! make sure it is set to 1 at the start and 0 on completion set rowcount 1 while exists (select FirstName,LastName,City from "TABLE" group by FirstName , LastName , City having count(*) > 1) begin delete T from "TABLE" T inner join (select FirstName,LastName,City from "TABLE" group by FirstName,LastName,City having count(*) > 1) dup on T.FirstName = dup.FirstName and T.LastName = dup.LastName and T.City = dup.City end go set rowcount 0 go Good Luck DBranscome Phoenix AZ David Branscome
modified on Tuesday, February 12, 2008 7:19 PM