Как получить самую последнюю и вторую последнюю дату для каждого идентификатора?

Question

Моя база данных SQL выглядит так

ID DATE 
1  2017-01-01
1  2017-01-03
1  2017-01-05
2  2017-01-06 
2  2017-01-07
2  2017-01-08
2  2017-01-11
3  2017-01-11

Как я могу получить максимальную (ДАТА) и вторую последнюю дату для каждого идентификатора, как показано ниже.

 ID MAXDATE     SECONDMAXDATE        
 1  2017-01-05  2017-01-03  
 2  2017-01-11  2017-01-08
 3  2017-01-11  2017-01-11 (or 'NONE')

PS: когда есть только 1 запись, SECONDMAXDATE может отображать либо MAXDATE, либо STRING (например, NONE)

Answer 1

Я думаю, что самый простой метод group_concat()/substring_index() "Взломать":

select id, max(date),
       substring_index(substring_index(group_concat(date order by date desc), ',', 2), ',', -1) as second_date
from t
group by id;

Примечание. Возвращает вторую дату в виде строки, а не даты. Вы можете преобразовать его обратно в дату, если хотите.

Также, group_concat() имеет максимальную длину по умолчанию 1024 байта. Этого достаточно для большинства целей (сто дат или около того). Если у вас больше дат на один идентификатор, то этот механизм, вероятно, не лучший механизм.

Answer 2

Например:

SELECT x.id
     , x.date
  FROM
     ( SELECT id
            , date
            , CASE WHEN @prev=id THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev:=id prev 
         FROM my_table
            , ( SELECT @prev:=null,@i:=0 ) vars
        ORDER
           BY id
            , date DESC
     ) x
 WHERE i <= 2;