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

[This](http://stackoverflow.com/questions/15969614/in-sql-how-to-select-the-top-2-rows-for-each-group) post should point you in the right direction. Possibly close as duplicate? — Hill, May 26 '16 at 13:17
Select x.* from my_table x join my_table y on y.something = x.something and y.other_thing <= x.other_thing group by x.something, x.other_thing having count(*) <= 2; — Strawberry, May 26 '16 at 13:23

PaulF · Accepted Answer · 2016-05-26T13:51:13.010

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.

score 2 · Answer 2 · answered May 26 '16 at 13:45

2

No need for nested queries to solve this:

SELECT t1.country, max(t1.date), max(t2.date)
FROM tbl t1
  JOIN tbl t2 ON t1.country = t2.country AND t2.date < t1.date
GROUP BY t1.country;

answered May 26 '16 at 13:45

wau

830
7
20

Perfect - very fast and clean. – The Onin Jan 12 '20 at 16:46

score 1 · Answer 3 · answered May 26 '16 at 13:20

This can be a pain. Here is one method:

select t.country, maxdate, max(t.date) as secondate
from tbl t left join
     (select country, max(date) as maxdate
      from tbl
      group by country
     ) c
     on t.country = c.country and  t.date < c.maxdate
group by t.country;

score 0 · Answer 4 · answered May 26 '16 at 14:29

0

Try this one

Select Country, MAX(Date) As Date From tbl GROUP BY Country Order By Date Desc Limit 2;

answered May 26 '16 at 14:29

Myo Myint Aung

147
1
3

You have misread my question. I have just provided two countries example but I would be more than two countries. :) – Shoaib Ijaz May 27 '16 at 05:03

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

4 Answers4