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.

On Dec 11, 8:04*pm, cate <catebekens... (AT) yahoo (DOT) com> wrote:
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:
http://download.oracle.com/docs/cd/B...s.htm#i1007032

I recently showed someone how to use the LEAD function in this recent
thread:
http://groups.google.com/group/comp....c7420e0e36231d

You can then compare the previous and next values using the DECODE
function:
http://download.oracle.com/docs/cd/B...ctions040..htm

SUM(DECODE(MY_COL,'x',1,0)*DECODE(LAG(MY_COL,1) OVER (ORDER BY
MYCOL2),'c',1,0))

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

On Dec 11, 8:43*pm, Charles Hooper <hooperc2... (AT) yahoo (DOT) com> wrote:
Worked great (lag - was a new one on me). Thanks
SELECT *
FROM ( SELECT one,
two,
three "this_three",
four,
LAG (three) OVER (ORDER BY four) AS Next_three
FROM tablex
ORDER BY four) lagit
WHERE this_three = 'c' AND next_three = 'x'