Given the following set of records, I'd like to return the first occurrence of 'changed' record.
Given (relevant fields):
ID STATE REF DATE
1 state_1 x 2010-12-01
2 state_1 x 2010-12-02
3 state_2 x 2010-12-02
4 state_2 x 2010-12-02
5 state_1 Y 2010-12-03
6 state_3 x 2010-12-03
7 state_4 x 2010-12-03
8 state_2 x 2010-12-03
9 state_1 x 2010-12-03
10 state_1 x 2010-12-04
11 state_2 x 2010-12-04
Desired return (for object ref X):
ID STATE REF DATE
1 state_1 x 2010-12-01
3 state_2 x 2010-12-02
6 state_3 x 2010-12-03
7 state_4 x 2010-12-03
8 state_2 x 2010-12-03
9 state_1 x 2010-12-03
11 state_2 x 2010-12-04
Further explanation: This is an auditing table that keeps state of objects, and I'd like to track the dates that a change occurred to the state of a certain object.
I'm playing with aggregates/analytic but starting to wonder if they would get me anywhere.
Try the following Query. Basically, you need to get the next value of STATE Columndisplay the current row if if the previous state is different from the current one.
create table state_data(
id number,
state varchar2(20),
ref varchar2(1),
date1 date);
insert into state_data values (1 ,'state_1','x',to_date('2010-12-01','YYYY-MM-DD'));
insert into state_data values (2 ,'state_1','x',to_date('2010-12-02','YYYY-MM-DD'));
insert into state_data values (3 ,'state_2','x',to_date('2010-12-02','YYYY-MM-DD'));
insert into state_data values (4 ,'state_2','x',to_date('2010-12-02','YYYY-MM-DD'));
insert into state_data values (5 ,'state_1','Y',to_date('2010-12-03','YYYY-MM-DD'));
insert into state_data values (6 ,'state_3','x',to_date('2010-12-03','YYYY-MM-DD'));
insert into state_data values (7 ,'state_4','x',to_date('2010-12-03','YYYY-MM-DD'));
insert into state_data values (8 ,'state_2','x',to_date('2010-12-03','YYYY-MM-DD'));
insert into state_data values (9 ,'state_1','x',to_date('2010-12-03','YYYY-MM-DD'));
insert into state_data values (10 ,'state_1','x',to_date('2010-12-04','YYYY-MM-DD'));
insert into state_data values (11 ,'state_2','x',to_date('2010-12-04','YYYY-MM-DD'));
commit;
-- and then the Query.
select id, state, ref, date1 from (
select id, state, ref, date1,
lag(state) over (order by id asc) prev_state
from state_data
)
where nvl(prev_state,'NULL') != nvl(state,'NULL');
ID STATE R DATE1
---------- -------------------- - ---------
1 state_1 x 01-DEC-10
3 state_2 x 02-DEC-10
5 state_1 Y 03-DEC-10
6 state_3 x 03-DEC-10
7 state_4 x 03-DEC-10
8 state_2 x 03-DEC-10
9 state_1 x 03-DEC-10
11 state_2 x 04-DEC-10
The above result has additional rows than what you showed in the output, but looking at your data, it seems like this is the correct result.