Oracle get row where column value changed

Question

Say I have a table, something like

ID     CCTR    DATE
-----  ------  ----------
1      2C      8/1/2018
2      2C      7/2/2018
3      2C      5/4/2017
4      2B      3/2/2017
5      2B      1/1/2017
6      UC      11/23/2016

There are other fields, but I made it simple. So I create a query where i have the date in descending order. I was to return the row where there was a change in CCTR. So in this case it would return ID 4. Basically i want to find the previous value of CCTR before it changed, in this case from 2B to 2C.

How do I do this? Ive tried to google it, but can't seem to find the right method.

Use the `LAG()` window function. – The Impaler Sep 13 '18 at 13:29 — The Impaler, Sep 13 '18 at 13:29

score 4 · Answer 1 · answered Sep 13 '18 at 13:39

4

You can use the LAG() window function to peek at the previous row and compare it. If your data is:

create table t2 (
  id number(6),
  cctr varchar2(10),
  date1 date
);

insert into t2 (id, cctr, date1) values (1, '2C', date '2018-08-01');
insert into t2 (id, cctr, date1) values (2, '2C', date '2018-07-02');
insert into t2 (id, cctr, date1) values (3, '2C', date '2017-05-04');
insert into t2 (id, cctr, date1) values (4, '2B', date '2017-03-02');
insert into t2 (id, cctr, date1) values (5, '2B', date '2017-01-01');
insert into t2 (id, cctr, date1) values (6, 'UC', date '2016-11-23');

Then the query would be:

select * from t2 where date1 = (
  select max(date1)
    from (
    select 
      id, date1, cctr, lag(cctr) over(order by date1 desc) as prev
      from t2
    ) x  
    where prev is not null and cctr <> prev
);

Result:

ID       CCTR        DATE1    
-------  ----------  -------------------
4        2B          2017-03-02 00:00:00

answered Sep 13 '18 at 13:39

The Impaler

45,731
9
39
76

I think this should report the UC row as well. – EvilTeach Sep 13 '18 at 13:41
It does, but I filtered it out using `MAX()`. He wants the last one only, right? – The Impaler Sep 13 '18 at 13:42
this worked. What if i wanted to get the first row after it changed? so in this case id 3 – dk96m Sep 13 '18 at 14:13
Ok, so if i add in employeeid, how do i get that same result, but for each employee. So there will be one line for each employee that fits the criteria above – dk96m Sep 13 '18 at 16:27
Thanks this example helped me understand this function better and write a query to find the date of the latest supervisor change for employees. – crumdev Mar 12 '20 at 17:34

Barbaros Özhan · Answer 2 · 2019-07-26T08:53:58.990

2

You may use first_value analytic function to detect the changes in CCTR column :

select fv as value, cctr
  from 
(
  with t(ID,CCTR) as
  (
   select 1,'2C' from dual union all
   select 2,'2C' from dual union all
   select 3,'2C' from dual union all
   select 4,'2B' from dual union all
   select 5,'2B' from dual union all
   select 6,'UC' from dual
  ) 
  select id, cctr, first_value(id) over (partition by cctr order by id ) fv
    from t
  order by id
)  
where id = fv;

VALUE  CCTR
-----  ----
  1     2C
  4     2B
  6     UC

Rextester Demo

edited Jul 26 '19 at 08:53

answered Sep 13 '18 at 13:57

Barbaros Özhan

59,113
10
31
55

1

I would use this method as the standard technique. – EvilTeach Sep 13 '18 at 14:04
yea, but that returns all the changes, i just want the last one where it changed, in this case ID 4 – dk96m Sep 13 '18 at 14:14
Let me change this around a little bit. Lets add in employeeid field. how do i get the row that meets my criteria for each employee – dk96m Sep 13 '18 at 16:55

Oracle get row where column value changed

2 Answers2

Linked