Table structure of logs table

This is the sample data. With this reference id ,many services logs are registered. CC_VERFIY_REGISTRATION service failed, hence 2 entries as one denotes latest entry. How do I identify last retry time of ANY API for a particular reference_id

select created_on
        ,service_name 
from thirdparty_service_logs tsl  
where reference_id ='7cb54c325450b4bc26'
group by service_name ,created_on

This gives the latest created_on for each service. But looking for latest created_on for reference_id . In this case, though the latest api call is for MOBILE_VERIFY, i'm looking for id 505's created_date column as output

CodePudding user response：

you can use CTE to prefetch reference_id and the corresponding max(created_on) according to your retry criteria, and then use it to get the target rows.

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

db<>fiddle

CodePudding user response：

You can find the last but, one record for the reference_id as given below:

declare @table table(id int,    reference_id varchar(50),   service_name varchar(50),   type int,   data varchar(50),   created_on datetime)

INSERT INTO @Table values
(504    ,'7cb54c325450b4bc26','CC_VERIFY_REGISTRATION', 0   ,'{{response_data}}','2020-09-14T07:53:42'),
(505    ,'7cb54c325450b4bc26','CC_VERIFY_REGISTRATION', 0   ,'{{response_data}}','2020-09-15T17:13:09'),
(506    ,'7cb54c325450b4bc26','MOBILE_VERIFY',  0           ,'{{response_data}}','2020-09-20T07:53:42');


SELECT top 1 * FROM @table  as ot where reference_id = '7cb54c325450b4bc26'
and created_on < (SELECT max(created_on) from @table as it where it.reference_id = ot.reference_id)
order by created_on desc