CODEWITHSUNDEEP
sqloracle-databasewindow
Jeremy
Jeremy

Reputation: 5435

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

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;

Upvotes: 1

Views: 1834

Answers (2)

Iain
Iain

Reputation: 76

See example below where I have used the WITH statement to "simulate" your table. In reality just replace "tbL" with your table name and remove the WITH clause.

The query finds the max version where the value changed and uses the id and the version to return the row from your table. It also deals with cases where there is only one version of a record without any updates.

    WITH tbl As
    (
    SELECT 1 As id, 3 As ver, 'Value2' As val FROM dual UNION ALL
    SELECT 1 As id, 2 As ver, 'Value2' As val FROM dual UNION ALL
    SELECT 1 As id, 1 As ver, 'Value1' As val FROM dual UNION ALL
    SELECT 2 As id, 3 As ver, 'Value3' As val FROM dual UNION ALL
    SELECT 2 As id, 2 As ver, 'Value2' As val FROM dual UNION ALL
    SELECT 2 As id, 1 As ver, 'Value1' As val FROM dual
    )
    SELECT t.*
      FROM tbl t
     WHERE (t.id, t.ver) IN 
    (
    SELECT z.id
         , MAX(z.ver) As max_ver
      FROM ( SELECT x.id
                  , x.ver
                  , x.val
                  , LAG(x.val) OVER (PARTITION BY x.id ORDER BY ver) As lag_val
                  , MIN(x.ver) OVER (PARTITION BY x.id) As min_ver
                  , MAX(x.ver) OVER (PARTITION BY x.id) As max_ver
               FROM tbl x
           ) z
     WHERE ( (z.min_ver = z.max_ver) -- where there is only one version
          OR (z.val != z.lag_val)    -- where the value has changed
           )
     GROUP BY z.id
    );

Upvotes: 0

SlimsGhost
SlimsGhost

Reputation: 2909

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

Upvotes: 1

Related Questions