Delete Duplicate Records from SQL Server Table

In the below case, if you want to see and delete the today records of product id 2 from the table t_duplicaterecords. You have to follow the below query. Here srid is the unique in table. RecordID may contain duplicate. The purpose here for us is to remove the duplicate records based on duplicate RecordID.
We can keep the minimum srid and delete the maximum srid for the duplicate records.
use databasename
go
Select * from databasename.dbo.t_duplicaterecords where datediff(dd,srdate,getdate())=0 and productid=2 and srid not in ( select min(srid) from databasename.dbo.t_duplicaterecords(nolock) where datediff(dd,srdate,getdate())=0 and productid=2 group by RecordID )

Below is the Procedure to delete duplicate records based on productid, startdate and enddate of that product ID.
Create Procedure P_DeleteDuplicate_t_duplicaterecords (@Productid int, @StartDate Datetime,@EndDate Datetime) as
Begin
Declare @cnt int
set @cnt = 1
Declare @srid table (Srid int Unique not null)
while (1=1)
Begin
Insert into @srid Select Top 5000 srid from databasename.dbo.t_duplicaterecords (nolock) where datediff(dd, SoldDate,@StartDate) <= 0 and datediff(dd, SoldDate, @EndDate) >= 0 and Productid=@Productid and ourprice>=0 and srid not in (select min(srid) from t_duplicaterecords(nolock) where datediff(dd, SoldDate,@StartDate) <= 0 and datediff(dd, SoldDate, @EndDate) >= 0 and Productid=@Productid and ourprice>=0
group by RecordID )
If @@rowcount =0 Return
Begin Try
Begin Transaction T1
insert into clashbackup.dbo.t_duplicaterecordsbackup (SRID,SoldDate,ClientID,Productid,BatchID,ourprice,RecordID,RecordSellerID,RecordSellerPrice,Email)
Select SRID,SoldDate,ClientID,Productid,BatchID,ourprice,RecordID,RecordSellerID,RecordSellerPrice,Email from databasename.dbo.t_duplicaterecords(nolock) where srid in (Select srid from @srid)
Delete from databasename.dbo.t_duplicaterecords where srid in (Select srid from @srid)
Commit Transaction T1
Delete from @srid
End Try
Begin Catch
If @@trancount>0
RollBack Transaction
End Catch
set @cnt = @cnt + 1
print @cnt
--WAITFOR DELAY '00:00:01'
End
End
If you want to remove whole table duplicates based on two column unique:
select srid,* from databasename.dbo.T_Duplicaterecords(nolock) where srid not in (select max(leadid) from databasename.dbo.T_Duplicaterecords(nolock) group by email,productid)
Once verification correct using above query, you can delete
delete from databasename.dbo.T_Duplicaterecords(nolock) where srid not in (select max(leadid) from databasename.dbo.T_Duplicaterecords(nolock) group by email,productid)
To see how many records duplicate:
select max(id),count(*) as [Counting] from T_Duplicaterecords(nolock)
group by productid,email
Having count(*)>1
To update duplicate records status in table:
UPDATE dbo.T_DuplicateRecords
SET RecordStatus = @newStatusBad FROM dbo.T_DuplicateRecords T1
,(
SELECT MIN(srid) AS Minsrid
,ProductID
,emailShort
,telephone FROM dbo.T_DuplicateRecords
WHERE RecordStatus IS NULL -- only records that still have status unknown
AND telephone IS NOT NULL -- exclude records with telephone NULL
GROUP BY ProductID ,emailShort ,telephone HAVING COUNT(*) > 1 ) derived
WHERE T1.srid > derived.Minsrid AND T1.RecordStatus IS NULL -- only records that still have status unknown
AND T1.telephone IS NOT NULL -- exclude records with telephone NULL
AND T1.ProductID = derived.ProductID AND T1.emailShort = derived.emailShort AND T1.telephone = derived.telephone;

Delete the duplicate records which is having some status. Take the min id and max id from the table and delete the records which is having status = 0. In this case status=0 must be deleted from the duplicate records.
SELECT MIN(ID), MAX(ID),Prodcutid,FilterID, COUNT(Prodcutid) FROM dbo.T_DuplicateRecords(nolock) GROUP BY Prodcutid, FilterID HAVING COUNT(Prodcutid) > 1 ORDER BY Prodcutid, FilterID, COUNT(Prodcutid) )
Take min(id) and max(id) from above query and delete the records where status=0
DELETE dbo.T_DuplicateRecords WHERE ID IN ( 171,417,241,734,170 ,4141,4142,4143,4144 ,4117,4118,4119,4120 ,4111,4112,4113,4114 ,4438,4439,4440,4441 ,4444,4235,4236,4237 ,4238,4494,4495,4496 ,4497,4536,4537,4538 ,4539,4543,4544,4545 ,4546,4557,4558,4559 ,4560,4563 ) AND STATUS=0