Remove Duplicates

[Dashley]

I have a zipcode table that has duplicate rows of data for multiple zipcodes.

Example:
select indx,zipcode,city,[state]
from uszips
where zipcode = '91789'
indx zipcode city state
76725 91789 WALNUT CA
70557 91789 WALNUT CA
70558 91789 WALNUT CA
70559 91789 WALNUT CA

I'd like to remove the duplicates for all rows for any zipcode. There are 76k rows so manually looking dosent seem like a good idea. The indx column is a self incrementing integer and is unique per row.

Thanks

 

[sparkbyte]

How About this

Declare @Example Table 
(
	Indx int,
	ZipCode int,
	City VarChar(8),
	[State] VarChar(2)
)

Insert Into @Example Values('76721','91781','ORANGE','CA')
Insert Into @Example Values('76722','91782','WALNUT','CA')
Insert Into @Example Values('76723','91783','CHERRY','CA')
Insert Into @Example Values('76724','91782','WALNUT','CA')
Insert Into @Example Values('76725','91783','CHERRY','CA')
Insert Into @Example Values('76726','91782','WALNUT','CA')
Insert Into @Example Values('76727','91782','WALNUT','CA')
Insert Into @Example Values('76728','91781','ORANGE','CA')
;
WITH Numbered
     AS ( SELECT   
			ZipCode,
			row_number() OVER ( PARTITION BY ZipCode ORDER BY ZipCode ) AS Nr
               FROM @Example
		)
--	  For Testing
--    SELECT  ZipCode
--    FROM    Numbered
--    WHERE   Nr > 1

-- Carefull this DELETES the Duplicate Records!!
	DELETE FROM Numbered
    WHERE Nr > 1

Select * from @Example

Example is here Removing Duplicates

Thanks

John Fuhrman