MYSQL: How to get Maximum and Second Maximum Date in single query

Question

I am trying to select Maximum Date and Second Max Date but can't get success.

This is table data.

ID Country  DATE 
1   Canada  2016-05-26
2   Canada  2016-05-25
3   Canada  2016-05-24

4   USA     2016-04-02
5   USA     2016-04-01
6   USA     2016-03-20

Expecting Output

Country  Max_Date    2nd_Date

Canada  2016-05-26  2016-05-25 
USA     2016-04-02  2016-04-01

What I have done so for:

Get Max Date using this query.

select Country, MAX(Date) from tbl GROUP BY (Country);

For Second Max date but failed to get result:

SELECT Country, MAX(date) FROM tbl WHERE Date NOT IN 

( select MAX(FROM) from tbl GROUP BY (Country)) GROUP BY (Country)

What should I try to get expected output. Thanks

Answer 1

Or you could try this

SELECT s.Country, Max(s.Date) Max_Date, 
(SELECT t.Date 
 FROM tbl t 
 Where s.Country=t.Country 
 ORDER BY Date DESC 
 LIMIT 1,1) 2nd_Date
FROM tbl s
GROUP BY COUNTRY;

The LIMIT clause is zero based, so using parameters 1,1 skips the first (ie max) value & returns just one value (2nd max).

NOTE - if the max date is duplicated the query will return Max_Date & 2nd_Date as the same value - if that is not what you want, then you can add DISTINCT to the inner query.