Get a most recent updated data from mysql database

Question

I need to retrieve a series of data for each id from a table. The data or the row value for the column need to be such that it is the second most recent value. For instance, I have Table- estimate_record as following

Id        value   last_updated
1         210     10/2018
1         205     11/2018
1         215     12/2018   -- current

I need to get the 205 for that particular id =1

I used Max(value), but it's getting 215 which is not right.

Answer 1

If you are running MySQL 8.0, you can do this with window functions:

select *
from (
    select t.*, row_number() over(partition by id order by last_updated desc) rn
    from mytable t
) t
where rn = 2

In earlier versions, one option uses a subquery:

select t.*
from mytable t
where t.last_updated = (
    select t1.last_updated
    from mytable t1
    where t1.id = t.id
    order by t1.last_updated desc
    limit 1 offset 1
)