Re: Find rows with a change in a field compared with the previous row

From: AK <AK_TIREDOFSPAM_at_hotmail.COM>
Date: 6 Jun 2005 07:36:55 -0700
Message-ID: <1118068615.644931.24590@g49g2000cwa.googlegroups.com>

create table status(
SESSION_ID number(2), LIGHT_ON char(1))
Table created

insert into status values(1,'y')
1 row inserted

insert into status values(2,'y')
1 row inserted

insert into status values(3,'n')
1 row inserted

insert into status values(4,'y')
1 row inserted

insert into status values(5,'n')
1 row inserted

insert into status values(6,'y')
1 row inserted

insert into status values(7,'y')
1 row inserted

insert into status values(8,'n')
1 row inserted

insert into status values(9,'n')
1 row inserted

select status.* from status join status previous_status on status.SESSION_ID = (previous_status.SESSION_ID + 1) where status.LIGHT_ON <> previous_status.LIGHT_ON order by status.SESSION_ID
SESSION_ID LIGHT_ON
---------- --------

         3 n
         4 y
         5 n
         6 y
         8 n

5 rows selected    

drop table status
Table dropped Received on Mon Jun 06 2005 - 09:36:55 CDT