Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Find rows with a change in a field compared with the previous row

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

From: Björn Wächter <Bjoern.Waechter_at_p3-solutions.de>
Date: Mon, 6 Jun 2005 16:49:17 +0200
Message-ID: <3gj643Fcnjk5U1@news.dfncis.de>


Hi,

Sorry I forgot one thing:
SESSION_ID is not necessarily +1 from
one entry to the next. In real my table
has one more column which i have to
search one first.
Example:

SESSION_ID | LIGHT_ON | LIGHT_ID


1          | Y        | 1
2          | Y        | 1
3          | Y        | 1
4          | Y        | 1
5          | N        | 1
6          | N        | 2
7          | N        | 1
8          | Y        | 1
9          | Y        | 2
10         | Y        | 3
11         | Y        | 1
12         | Y        | 1
13         | Y        | 1
14         | N        | 1
15         | N        | 2

I only want ot know the status oh Light 1.

Sorry and thanks Björn

>
> 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:49:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US