sql - Detecting the change on the certain column in the history table -
consider following data:
history.data ======================================= id |data_id| col1 | col2 | date ---+-------+-------+-------+----------- 1 |1 | 123 | 321 | 2017-08-01 2 |1 | 124 | 321 | 2017-08-03 3 |2 | 222 | 555 | 2017-08-05 4 |2 | 242 | 555 | 2017-08-07 5 |2 | 242 | 333 | 2017-08-11
so history_data
table keep changes in table. right need date of last change every present entry of data
in col1
column. in case desired output should be
data_id | date --------+----------- 1 | 2017-08-03 2 | 2017-08-07
i need in following context:
with cte1 ( select distinct on(data_id) data_id, date::date data d join history.data hd on hd.data_id = d.id order d.id, hd.date desc )
so can see, i'm getting date of last record change, regardless of on column change occured.
could please me that?
you can use lag()
previous prev_col1
value , prev_col1 <> col1
identify rows change occured:
select distinct on(data_id) * ( select lag(col1) on (partition data_id order d.id) prev_col1, d.id, col1, data_id, date::date data d join history.data hd on hd.data_id = d.id ) t prev_col1 <> col1 or prev_col1 null order id desc
the prev_col1 null
condition needed groups 1 member per data_id
, assumes first member qualifies change.
Comments
Post a Comment