-1

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
Dani
  • 47
  • 1
  • 9

1 Answers1

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
)
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thanks, but I am using MySQL 5.5. – Dani Sep 15 '20 at 20:57
  • @Sam: ok. I edited my answer with a solution for earlier versions. – GMB Sep 15 '20 at 20:59
  • let's say I added two more lines 2 214 12/2018 3 300 11/2019 3 400 12/2019 I need to get the 205 for that particular id =1, but 300 for id = 3. Also, for that particular id, I also need to find the difference between the current and the last updated one. – Dani Sep 15 '20 at 21:12
  • @Sam: it is not a good practice here on SO to change the question after answers were posted. I can only recommend that you [ask a new question](https://stackoverflow.com/questions/ask). – GMB Sep 15 '20 at 21:43
  • Sure. Although I have this account for a few years now, I rarely use it. I will ask another question. – Dani Sep 16 '20 at 11:39