Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g49g2000cwa.googlegroups.com!not-for-mail From: "AK" Newsgroups: comp.databases.oracle.misc Subject: Re: Find rows with a change in a field compared with the previous row Date: 6 Jun 2005 07:36:55 -0700 Organization: http://groups.google.com Lines: 57 Message-ID: <1118068615.644931.24590@g49g2000cwa.googlegroups.com> References: <3gj35bFcctikU1@news.dfncis.de> NNTP-Posting-Host: 205.156.188.254 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1118068622 17870 127.0.0.1 (6 Jun 2005 14:37:02 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 6 Jun 2005 14:37:02 +0000 (UTC) In-Reply-To: <3gj35bFcctikU1@news.dfncis.de> User-Agent: G2/0.2 Complaints-To: groups-abuse@google.com Injection-Info: g49g2000cwa.googlegroups.com; posting-host=205.156.188.254; posting-account=dcyjXg0AAAAAr7KEmYS5ttp6D-QeNceH Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:119654 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