How to delete duplicates with unique date_time

1

I'm trying to removed duplicates that were created with with and "hour" (hh) difference. Example, 1/1/2016 2:10:55 AM ... vs ... 1/1/2016 4:10:55 where all other values are the same (meaning other columns).

Below is the SQL query I used to find my 'unique' duplicates. I've reviewed them and they are ready to be deleted. However, I'm struggling on how to delete these records because of the unique Date_Time. I've looked at numerous examples but they all assume 'exact duplicates' occur but they don't because of this anomaly. Any recommendations?

SELECT Script_Log.Script_Name, Script_Log.Date_Time, Script_Log.Person, Script_Log.Computer, Script_Log.Area, Count()
FROM Script_Log
GROUP BY Script_Log.[Script_Name], Script_Log.[Person], Script_Log.[Computer], Script_Log.[Area], Script_Log.[Date_Time]
HAVING (((Count())>1) And ((Left(Right([Date_Time],8),5))=Left(Right(Script_Log.Date_Time,8),5)));

2

Can you provide sample data and expected output.

You might want to look at ROW_NUMBER

3

Hi,

Please try below code

Declare @Script_Log table(logid int,Script_Name Varchar(100),Date_Time Datetime,Person Varchar(100),Computer Varchar(100),Area Varchar(100))

Insert into @Script_Log values(1,'abc','1/1/2016 2:10:55 AM','xyz','dell','india')
Insert into @Script_Log values(2,'abc','2/1/2016 4:10:55 AM','xyz','dell','india')
Insert into @Script_Log values(3,'cde','1/1/2016 2:10:55 AM','xyz','dell','india')
Insert into @Script_Log values(4,'cde','1/1/2016 4:10:55 AM','xyz','dell','india')
Insert into @Script_Log values(5,'cde','1/1/2016 6:10:55 AM','xyz','dell','india')
Insert into @Script_Log values(6,'qwe','1/1/2016 2:10:55 AM','xyz','dell','india')
Insert into @Script_Log values(7,'qwe','2/1/2016 2:10:55 AM','xyz','dell','india')
Insert into @Script_Log values(8,'qwe','1/1/2016 2:10:55 AM','xyz','dell','india')

delete from @Script_Log where logid in(
Select logid from (
Select *,row_number() over(partition by Script_Name,convert(varchar(100),Date_Time,101) order by Script_Name) as 'DistinctRecord'
from @Script_Log) tt where DistinctRecord>1
)

Select * from @Script_Log