Mysql: Getting the 2nd earliest date for each group

Question

Desired outcome: I want to get the 2nd earliest date for each group.

Current: I am getting the earliest date for each group

Query:

select * from
(select * from profile_match
where match_available_on is not null
order by profile_id asc, match_available_on asc)
as P1
group by P1.profile_id

Example: Link

From the example, I want to get:

• 1, '2015-05-20 03:50:11'
• 2, '2015-05-16 03:50:09'

And I hope that the query is not hard-coded with specific profile_id (1 or 2) as my actual data got lots of profile_id. Thanks guys!

Answer 1

This query will give you the results you want:

select t.profile_id, min(t.match_available_on )
  from profile_match t
    inner join (
      select profile_id, min(match_available_on) match_available_on
        from profile_match
        group by profile_id
    ) q
    on t.profile_id = q.profile_id
      and t.match_available_on > q.match_available_on
  group by t.profile_id;

By finding the minmatch_available_on, we can then range join against that, and choose the minimum joined value. This isn't going to be amazingly performant, but it will get the job done.

updated your demo here.

It will not return a row for a profile which has only one date available, as there is no 'second earliest'.