peter peter - 1 month ago 7
MySQL Question

how to get latest date data from table

I want to get rows of the latest date and the next latest date. I have searched lots of sites and I have still no idea how to write the sql statement.

From this

id inputDate refNo
1 2016-09-22 16092201820X0X
2 2016-09-22 16092200230X1X
3 2016-09-22 16092200810X3X
4 2016-09-21 16092200430X2X
5 2016-09-21 16092201460X7X
6 2016-09-21 16092200430X1X
7 2016-09-20 16092202260X3X
8 2016-09-20 16092200330X6X
9 2016-09-20 16092200610X3X
10 2016-09-19 16092200430X8X
11 2016-09-19 16092200450X1X


to this

id inputDate refNo
1 2016-09-22 16092201820X0X
2 2016-09-22 16092200230X1X
3 2016-09-22 16092200810X3X
4 2016-09-21 16092200430X2X
5 2016-09-21 16092201460X7X
6 2016-09-21 16092200430X1X

Answer

In ANSI standard SQL, you would use dense_rank():

select t.*
from (select t.*, dense_rank() over (order by inputdate desc) as seqnum
      from t
     ) t
where seqnum <= 2;

MySQL does not support the ANSI-standard window functions (unlike basically all other major databases).

In MySQL, this is trickier. Here is a convenient method:

select t.*
from t join
     (select inputdate
      from (select distinct inputdate from t) t
      order by inputdate desc
      limit 2
     ) tt
     on t.inputdate = tt.inputdate;

You don't actually need the second subquery, so you can do:

select t.*
from t join
     (select distinct inputdate
      from t
      order by inputdate desc
      limit 2
     ) tt
     on t.inputdate = tt.inputdate;

But it can be confusing to remember whether select distinct happens before or after the order by.

Comments