Query to find second largest value from every group

Question

I have three tables:

1. project: project_id, project_name
2. milestone: milestone_id, milestone_name
3. 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?

Answer 1

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;