【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 2^nd 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:

A2: ~.m(2) means getting the 2^nd record of a group.