SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Drop Table thirdparty_service_logs
Msg 3701 Level 11 State 5 Line 1
Cannot drop the table 'thirdparty_service_logs', because it does not exist or you do not have permission.

Create Table thirdparty_service_logs (id int, reference_id varchar(100), [service_name] VarChar(100), [type] Int, data VarChar(100), created_on Datetime)
Insert Into thirdparty_service_logs (id, reference_id, [service_name], [type], data, created_on)
Values (504, '7cb54c325450b4bc26', 'CC_VERIFY_REGISTRATION', 0, '{{response_data}}', '20200914 07:53:42'),
       (505, '7cb54c325450b4bc26', 'CC_VERIFY_REGISTRATION', 0, '{{response_data}}', '20200915 17:13:09'),
       (506, '7cb54c325450b4bc26', 'MOBILE_VERIFY', 0, '{{response_data}}', '20200920 7:53:42'),
       (507, '7cb54c325450b4bc30', 'Another servise witout retry', 0, '{{response_data}}', '20200920 7:53:42')
4 rows affected

With CTE As (
	Select reference_id, Max(created_on) As Lst
	From thirdparty_service_logs
	Group by reference_id
	Having Count(*)>1)
Select *
From thirdparty_service_logs As t
Where Exists (Select * From CTE Where reference_id=t.reference_id And lst=t.created_on)
Order by reference_id


id
reference_id
service_name
type
data
created_on


506
7cb54c325450b4bc26
MOBILE_VERIFY
0
{{response_data}}
2020-09-20 07:53:42.000