Re: count specific data changes in adjacent ordered rows

From: Charles Hooper <>
Date: Fri, 11 Dec 2009 18:43:06 -0800 (PST)
Message-ID: <>

On Dec 11, 8:04 pm, cate <> wrote:
> a b c time
> a b d time
> a b e time
> a b c time
> a b x time
> a b d time
> I want to count all the c -> x transitions found in adjacent rows.
> The table is ordered.  The count is one in the above example.
> Oracle SQL
> Thank you.

I hope that you mean that you are using an ORDER BY clause when retrieving the rows from the table. Inserting the rows into a table in a specific order does not mean that Oracle stores the rows in that particular order, nor does it mean that Oracle will retrieve the rows in that specific order.

Take a look at the LAG or LEAD analytic functions to look at the previous or next row, respectively:

I recently showed someone how to use the LEAD function in this recent thread:

You can then compare the previous and next values using the DECODE function:


Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Dec 11 2009 - 20:43:06 CST

Original text of this message