mardi 4 août 2015

ROW_NUMBER query

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?

Aucun commentaire:

Enregistrer un commentaire