Mysql: getting second earliest date for each group
Desired output : I want to get the second 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
In this example, I want to get:
- 1, '2015-05-20 03:50:11'
- 2, '2015-05-16 03:50:09'
And I hope the request is not hardcoded with a specific profile_file (1 or 2) as my actual data received a lot of profile_id. Thanks guys!
+3
source to share
1 answer
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;
Once match_available_on
we find min , we can then concatenate it against that and select the minimum concatenated value. It won't be overwhelming, but it will get the job done.
It will not return a string for a profile that has only one available date, since there is no "second early" one.
+1
source to share