How to retrieve the latest and second latest date for each ID?

Question

My SQL database looks like this

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

How can I retrieve the Max(DATE) and second latest date for each ID as in below.

 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: when there is only 1 record, SECONDMAXDATE can display either the MAXDATE or a STRING (eg. NONE)

tag the database being used. – Vamsi Prabhala Oct 04 '17 at 16:09 — Vamsi Prabhala, Oct 04 '17 at 16:09

score 4 · Accepted Answer · answered Oct 04 '17 at 16:14

4

I think the simplest method is the group_concat()/substring_index() "hack":

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;

Note: This returns the second date as a string rather than a date. You can convert it back to a date if you like.

Also, group_concat() has a default maximum length of 1,024 bytes. This is sufficient for most purposes (a hundred dates or so). If you have more dates per id, then this mechanism is probably not the best mechanism.

answered Oct 04 '17 at 16:14

Gordon Linoff

1,242,037
58
646
786

I was slowly preparing an answer then Gordon arrived again with the best solution – Thomas G Oct 04 '17 at 16:17
Thank you Gordon. Yes I do have thousands of dates.. If there a way I could merge 2 tables? one with MAXDATE, and the other with SECONDDATE? If so how could I get the SECONDDATE for each ID? – SamanthaAlexandria Oct 04 '17 at 16:27
Also, how would I be able to convert it back to a date in the same query? – SamanthaAlexandria Oct 04 '17 at 16:36
1

@SamanthaAlexandria . . . Just use `date()` around the expression. – Gordon Linoff Oct 04 '17 at 23:25

score 0 · Answer 2 · answered Oct 04 '17 at 17:26

E.g.:

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;

How to retrieve the latest and second latest date for each ID?

2 Answers2