Get retry time from logs table using SQL

Question

Table structure of logs table

id	reference_id	service_name	data	created_on
504	7cb54c325450b4bc26	CC_VERIFY_REGISTRATION	{{response_data}}	2020-09-14 07:53:42
505	7cb54c325450b4bc26	CC_VERIFY_REGISTRATION	{{response_data}}	2020-09-15 17:13:09
506	7cb54c325450b4bc26	MOBILE_VERIFY	{{response_data}}	2020-09-20 7:53:42

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

*This gives the latest created_on for each service*. No, it doesn't. You're grouping unique records because `created_on` is unique. That aside, from your description it's not clear why you don't simply filter out records having `service_name = MOBILE_VERIFY`. — Gert Arnold, Dec 31 '21 at 09:58
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Dec 31 '21 at 11:01

Anton Grig · Answer 1 · 2021-12-31T11:50:41.557

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

Result:

id	reference_id	service_name	type	data	created_on
506	7cb54c325450b4bc26	MOBILE_VERIFY	0	{{response_data}}	2020-09-20 07:53:42.000

score 0 · Answer 2 · answered Dec 31 '21 at 10:34

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

id	reference_id	service_name	type	data	created_on
505	7cb54c325450b4bc26	CC_VERIFY_REGISTRATION	0	{{response_data}}	2020-09-15 17:13:09.000

Get retry time from logs table using SQL

2 Answers2