Oracle SQL -- Find the most recent data where a column changed in a history table

Question

I am trying to find the most recent date or version where a certain column's value is different than it was in the previous state.

Here is an example of what my history table would look like:

ID_OF_THING  VERSION_NUMBER   DATA
1            3                'Value2'
1            2                'Value2'
1            1                'Value1'
2            3                'Value3'
2            2                'Value2'
2            1                'Value1'

In this case, the answer for id_of_thing 1 is version 2, because it's the highest version number where the previous version number has different data. The answer for id_of_thing 2 is version 3.

I'm not sure how to even get started here. If I just wanted the most recent version number, it would be as simple as:

select ID_OF_THING, MAX(VERSION_NUMBER)
GROUP BY ID_OF_THING;

Answer 1

This is most easily done with analytic (aka windowing) functions, in this case lead() or lag() to look at the next or previous row's data. The following should work for you (replace the table name I used ("test") with whatever you called your table):

select 
    id_of_thing, 
    version_with_latest_change=max(version_number) 
from (
    select 
        id_of_thing, version_number, data, 
        previous_data=lag(data) over (
            partition by id_of_thing 
            order by version_number
        ) 
    from test
) x
where data <> previous_data
group by id_of_thing