Select second most recent date from inner join-CodePudding

I have this query :

SELECT 
    companies.display_name, companies.pay_schedule_id, 
    pay_schedule_periods.schedule_id, 
    pay_schedule_periods.created_at
FROM
    companies
INNER JOIN 
    pay_schedule_periods ON pay_schedule_id = pay_schedule_periods.schedule_id
ORDER BY 
    companies.display_name, pay_schedule_periods.created_at DESC;

I get this result :

How can I select only the second most recent created_at date from each unique display_name ?

CodePudding user response：

You could use row_number to assign a sequence to your dates and apply this before joining, then include as part of your join criteria, such as:

select c.display_name, c.pay_schedule_id, psp.schedule_id, psp.created_at
from companies c
join (
  select pay_schedule_id, created_at, 
    Row_Number() over(partition by pay_schedule_id order by created_at desc) rn
  from pay_schedule_periods 
)psp on psp.schedule_id = c.pay_schedule_id and rn = 2
order by c.display_name, psp.created_at desc;

You could also apply this using a lateral join which would simplify further.