I have three tables:
project: project_id, project_name
milestone: milestone_id, milestone_name
project_milestone: id, project_id, milestone_id, completed_date
I want to get the second highest completed_date and milestone_id from project_milestone grouped by project_id. That is I want to get the milestone_id of second highest completed_date for each project. What would be the correct query for this?
I think you can do what you want with the project_milestone
table and row_number()
:
select pm.*
from (select pm.*,
row_number() over (partition by project_id order by completed_date desc) as seqnum
from project_milestone pm
where pm.completed_date is not null
) pm
where seqnum = 2;
If you need to include all projects, even those without two milestones, you can use a left join
:
select p.project_id, pm.milestone_id, pm.completed_date
from projects p left join
(select pm.*,
row_number() over (partition by project_id order by completed_date desc) as seqnum
from project_milestone pm
where pm.completed_date is not null
) pm
on p.project_id = pm.project_id and pm.seqnum = 2;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With