Finding and Deleting Duplicate Records

I have a table that has many duplicate records and I need to delete all but
one of the duplicate records. Is there a way I can identify these records
with a sql statement?
Example record:
(Comment_id, site_id, date_entered, user_id, comment)
6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
non-franchise signage.
8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
non-franchise signage.Mike,
Depending on the size of the comment field you could do something like this:
SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
FROM <TABLENAME>
HTH
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>I have a table that has many duplicate records and I need to delete all but
> one of the duplicate records. Is there a way I can identify these records
> with a sql statement?
> Example record:
> (Comment_id, site_id, date_entered, user_id, comment)
> 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.
> 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.|||Thanks, but how would I delete all the other records that I don't want?
"Jerry Spivey" wrote:

> Mike,
> Depending on the size of the comment field you could do something like thi
s:
> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> FROM <TABLENAME>
> HTH
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>
>|||Mike,
Can you post the DDL for the table(s) so I don't have to guess about the
keys?
Thanks
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...[vbcol=seagreen]
> Thanks, but how would I delete all the other records that I don't want?
> "Jerry Spivey" wrote:
>|||CREATE TABLE [dbo].[StatusRptComments] (
[Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
[Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI
_AS
NULL ,
[comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
(
[Comment_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
"Jerry Spivey" wrote:

> Mike,
> Can you post the DDL for the table(s) so I don't have to guess about the
> keys?
> Thanks
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
>
>|||Mike,
What criteria do you want for the DELETE? I.e., ok the comment column is
the same for x records but the other columns may be different i.e,
comment_id, dateentered, etc... Which one record do you want to retain?
Thanks
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...[vbcol=seagreen]
> CREATE TABLE [dbo].[StatusRptComments] (
> [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
> [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_
CI_AS
> NULL ,
> [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
> CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
> (
> [Comment_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> "Jerry Spivey" wrote:
>|||Sorry...I want to keep the entry with the latest date. Thanks for your
patience with my not providing complete details up front.
"Jerry Spivey" wrote:

> Mike,
> What criteria do you want for the DELETE? I.e., ok the comment column is
> the same for x records but the other columns may be different i.e,
> comment_id, dateentered, etc... Which one record do you want to retain?
> Thanks
> Jerry
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
>
>|||Mike,
Try:
BEGIN TRAN
DELETE #STATUSRPTCOMMENTS
FROM #STATUSRPTCOMMENTS S2
WHERE SITE_NO = S2.SITE_NO AND ADMINID = S2.ADMINID AND COMMENT = S2.COMMENT
AND S2.DATEENTERED <> (SELECT MAX(DATEENTERED) FROM #STATUSRPTCOMMENTS S1
WHERE S1.SITE_NO = S2.SITE_NO)
--ROLLBACK TRAN
--OR
--COMMIT TRAN
My mind is shot today so use this explicit tran and see if it works if not
roll back and tell me what is wrong and I'll look at it this weekend.
HTH
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:6476D0E1-1054-429E-A456-35982AA76A29@.microsoft.com...[vbcol=seagreen]
> Sorry...I want to keep the entry with the latest date. Thanks for your
> patience with my not providing complete details up front.
> "Jerry Spivey" wrote:
>|||That works great...thanks Jerry.
"Jerry Spivey" wrote:

> Mike,
> Try:
> BEGIN TRAN
> DELETE #STATUSRPTCOMMENTS
> FROM #STATUSRPTCOMMENTS S2
> WHERE SITE_NO = S2.SITE_NO AND ADMINID = S2.ADMINID AND COMMENT = S2.COMME
NT
> AND S2.DATEENTERED <> (SELECT MAX(DATEENTERED) FROM #STATUSRPTCOMMENTS S1
> WHERE S1.SITE_NO = S2.SITE_NO)
> --ROLLBACK TRAN
> --OR
> --COMMIT TRAN
> My mind is shot today so use this explicit tran and see if it works if not
> roll back and tell me what is wrong and I'll look at it this weekend.
> HTH
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:6476D0E1-1054-429E-A456-35982AA76A29@.microsoft.com...
>
>sql