0

I extracted from database a result set like this:

date       | bussines attribute 
===========|======================
2017-07-20 | A
2017-07-16 | A
2017-07-12 | A
2017-07-01 | B
2017-06-01 | B
2017-05-22 | A
2017-04-11 | C
2017-03-23 | C

What i want to achieve, is to get the last record before every change of the business attribute I mentioned:

2017-07-20 | A
2017-07-01 | B
2017-05-22 | A
2017-04-11 | C

Group by would work here for me, if not the case when the user can switch back to one of the previous values. Any ideas how to workaround it in MySQL?

pidabrow
  • 966
  • 1
  • 21
  • 47
  • 2
    This is called gaps and island problem.. It is easy to do with `Row_Number` unfortunately Mysql does not supports it – Pரதீப் Jul 20 '17 at 09:25
  • 1
    This should help you [How to group continuous ranges using MySQL](https://stackoverflow.com/questions/12276087/how-to-group-continuous-ranges-using-mysql) – Pரதீப் Jul 20 '17 at 09:26
  • thanks Pரதீப் for providing the name of this problem. One of my problems was also that I didn't know what phrase should I search for :) I'll look at the link you mentioned, thx. – pidabrow Jul 20 '17 at 09:30
  • Pரதீப் it works, thank you very much! Convert your comments to an answer please and I'll mark it as accepted. – pidabrow Jul 21 '17 at 16:50
  • Post your query as answer and mark it as answer :) – Pரதீப் Jul 21 '17 at 16:52

0 Answers0