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;

SlimsGhost · Accepted Answer · 2014-07-03 23:19:04Z

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

Iain · Accepted Answer · 2014-07-04 08:43:29Z

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 );

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related