mysql select row before max value-CodePudding

My Requirement is to get the row before the max(create_date).

SELECT servicecalls_servicecall_id, max(created_at) FROM `service_followup_details` where servicecalls_servicecall_id IN (SELECT service_call_id from service_calls where status=2) group by servicecalls_servicecall_id

can you tell me how can i do it by mysql query

CodePudding user response：

Get the max(create_at), then pick the one which is less than that max value.

select servicecalls_servicecall_id,created_at 
from `service_followup_details` 
where created_at<(
    SELECT  max(created_at) 
    FROM `service_followup_details` 
    where servicecalls_servicecall_id IN 
        (SELECT service_call_id from service_calls 
        where status=2) 
    group by servicecalls_servicecall_id
    )
order by created_at desc 
limit 1
;

CodePudding user response：

Well you can achieve this with the help of ROW_NUMBER() function. Check my implementation below. What I've done is, I'm sorting the records by created_at in a DESC order. Off course, this is partitioned by the servicecalls_servicecall_id so the sequence will restart for each new servicecalls_servicecall_id. Then you can simply pick the records that have row_number=2 to get the closest record to the max created_date.

SELECT servicecalls_servicecall_id,
ROW_NUMBER() OVER (partition by servicecalls_servicecall_id ORDER BY created_at DESC) AS row_number
FROM service_followup_details 
WHERE servicecalls_servicecall_id IN (
  SELECT service_call_id FROM service_calls WHERE status=2
) 
AND row_number = 2;