MYSQL: How to get Maximum and Second Maximum Date in single query
<p>I am trying to select Maximum Date and Second Max Date but can't get success.</p>

<p>This is table data.</p>

<pre><code>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
</code></pre>

<p>Expecting Output</p>

<pre><code>Country  Max_Date    2nd_Date

Canada  2016-05-26  2016-05-25 
USA     2016-04-02  2016-04-01
</code></pre>

<p>What I have done so for:</p>

<p>Get Max Date using this query.</p>

<pre><code>select Country, MAX(Date) from tbl GROUP BY (Country);
</code></pre>

<p>For Second Max date but failed to get result:</p>

<pre><code>SELECT Country, MAX(date) FROM tbl WHERE Date NOT IN 

( select MAX(FROM) from tbl GROUP BY (Country)) GROUP BY (Country)
</code></pre>

<p>What should I try to get expected output. Thanks</p>