分组后获取每组的指定记录

【问题】

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 got lots of profile_id. Thanks guys!

【回答】

MySQL 没有窗口函数，写起来比较麻烦，用集算器会很方便：

	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) 表示获取本组记录的第 2 条。