ROW_NUMBER query

Question

I have a table:

Trip  Stop  Time 
-----------------
1     A     1:10
1     B     1:16
1     B     1:20
1     B     1:25
1     C     1:31
1     B     1:40
2     A     2:10
2     B     2:17
2     C     2:20
2     B     2:25  

I want to add one more column to my query output:

Trip  Stop  Time Sequence
-------------------------
1     A     1:10   1
1     B     1:16   2 
1     B     1:20   2
1     B     1:25   2
1     C     1:31   3
1     B     1:40   4 
2     A     2:10   1
2     B     2:17   2
2     C     2:20   3
2     B     2:25   4  

The hard part is B, if B is next to each other I want it to be the same sequence, if not then count as a new row.

I know

row_number over (partition by trip order by time)
row_number over (partition by trip, stop order by time)

None of them will meet the condition I want. Is there a way to query this?

Answer 1

create table test
(trip number
,stp  varchar2(1)
,tm   varchar2(10)
,seq  number);

insert into test values (1,     'A',     '1:10',   1);
insert into test values (1,     'B',     '1:16',   2); 
insert into test values (1,     'B',     '1:20',   2);
insert into test values (1 ,    'B',     '1:25',   2);
insert into test values (1 ,    'C',     '1:31',   3);
insert into test values (1,     'B',     '1:40',   4);
insert into test values (2,     'A',     '2:10',   1);
insert into test values (2,     'B',     '2:17',   2);
insert into test values (2,     'C',     '2:20',   3);
insert into test values (2,     'B',     '2:25',   4);

select t1.*
      ,sum(decode(t1.stp,t1.prev_stp,0,1)) over (partition by trip order by tm) new_seq
from  
     (select t.*
            ,lag(stp) over (order by t.tm) prev_stp
      from   test t
      order  by tm) t1
;

  TRIP S TM                SEQ P    NEW_SEQ
------ - ---------- ---------- - ----------
     1 A 1:10                1            1
     1 B 1:16                2 A          2
     1 B 1:20                2 B          2
     1 B 1:25                2 B          2
     1 C 1:31                3 B          3
     1 B 1:40                4 C          4
     2 A 2:10                1 B          1
     2 B 2:17                2 A          2
     2 C 2:20                3 B          3
     2 B 2:25                4 C          4

 10 rows selected 

You want to see if the stop changes between one row and the next. If it does, you want to increment the sequence. So use lag to get the previous stop into the current row.

I used DECODE because of the way it handles NULLs and it is more concise than CASE, but if you are following the text book, you should probably use CASE.

Using SUM as an analytic function with an ORDER BY clause will give the answer you are looking for.