为mySQL 5中的每个组选择第二个最高值

Question

此代码获取每个id 的id和最新时间戳

select id, max(start_time) as start1, max(end_time) as end1 from table group by id

如何获得第二高的时间戳start2和end2？

Answer 1

一个选项使用相关的子查询：

select 
id, 
max(start_time) as start1, 
max(end_time) as end1,
(
select t1.start_time
from mytable t1
where t1.id = t.id
order by t1.start_time desc
limit 1, 1
) start2,
(
select t1.end_time
from mytable t1
where t1.id = t.id
order by t1.end_time desc
limit 1, 1
) end2
from mytable t
group by id