[RESOLVED] Removing Duplicates in MSSQL

[Derokorian]

Hi guys,
I'm having a bit of trouble removing duplicate rows from a table in MSSQL. The problem is everything in the row is duplicate EXCEPT the primary key and everything I've found using google shows how to remove when a key is duplicated. However there may be 2 rows for example that are unique, but are almost identical so I can't really do WHERE column = column unless I want to list all the columns (in this case 27).

Also, I need to make sure that the LOWEST primary key row is the one that is maintained. I can't use PHP to do this and I just can't wrap my head around a query that will work. Basically ATM I'm trying to create a SELECT to get all the MAX ids of the duplicated rows and once I get that working (even if it only finds one row when its duplicated 5 times that's fine I can just keep rerunning it) use a DELETE ... WHERE id IN (select max ids).

Here is the table structure:

CREATE TABLE [dbo].[Organization](
	[OrganizationId] [int] IDENTITY(1,1) NOT NULL,
	[OrganizationName] [nvarchar](100) NULL,
	[Address1] [nvarchar](100) NULL,
	[Address2] [nvarchar](100) NULL,
	[ZipCode] [nvarchar](10) NULL,
	[DateEstablished] [nvarchar](100) NULL,
	[CountyId] [int] NULL,
	[StateId] [int] NULL,
	[CityID] [int] NULL,
	[OrgProfile] [varchar](max) NULL,
	[InactiveDate] [datetime] NULL,
	[Address3] [varchar](100) NULL,
	[PrimaryContactId] [int] NULL,
	[RespondentLogo] [varchar](100) NULL,
	[EmployeeLogo] [varchar](100) NULL,
	[OrganizationTypeId] [int] NULL,
	[ReceiveReferralEMail] [bit] NOT NULL,
	[ReceiveAssignmentEMail] [bit] NOT NULL,
	[FormsArePrivateByDefault] [bit] NOT NULL,
	[ExternalId] [varchar](50) NULL,
	[TrainingProviderId] [int] NULL,
	[AcctVendorNumber] [varchar](20) NULL,
	[EIN] [varchar](20) NULL,
	[IsVendor] [bit] NOT NULL,
	[IsTrainingProvider] [bit] NOT NULL,
	[IsServiceProvider] [bit] NOT NULL,
	[IsPartner] [bit] NOT NULL,
	[ParentOrganizationId] [int] NOT NULL,
	[ViewAllRespondents] [bit] NULL,
 CONSTRAINT [PK_Organization_OrganizationId] PRIMARY KEY CLUSTERED 
(
	[OrganizationId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Hope some body can shed some light as my brain is just racked by this right now. Thanks in advance!

[Weedpacket]

The first idea that occurs to me (not knowing in advance if there's something better) would be something like

Delete from [dbo].[Organization]
Where [OrganizationId] not in(
	Select min([OrganizationId]) from [dbo].[Organization] Group By
		[OrganizationName], [Address1], [Address2], ....
	)

Rather than list the columns manually, you might be able to get them by querying the schema.

[Derokorian]

OMG not in and min - why didn't I think of that... Thank you so much weed that worked perfectly! More specifically I now have 159 distinct rows AND 159 rows... as opposed to 159 distinct rows (not including PK) and 487 rows total.