Get Specified Record(s) from a Group
【Question】
Table profile_match:
profile_id match_available_on
1 NULL
1 2015-05-12 03:50:09
1 2015-05-20 03:50:11
1 2015-05-31 03:50:17
2 2015-05-16 03:50:09
2 2015-05-11 03:52:30
2 2015-05-21 03:50:16
2 2015-05-28 03:50:17
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 has lots of profile_id.
【Answer】
MySQL doesn’t provide window functions. So it’s complicated to do this in MySQL. You can handle it in esProc SPL (Structured Process Language). Below is the SPL code:
A |
|
1 |
$select profile_id,match_available_on from profile_match where match_available_on!=null order by match_available_on |
2 |
=A1.group(profile_id).(~.m(2)) |
A2: ~.m(2) means getting the 2nd record of a group.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL