1

For example if this is my table -

SeqNo   Gap
20  Start
21  End
29  Start
30  End
42  Start
43  End
49  Start
50  Start
51  Start
52  Start
53  Start
54  Start
55  End
220 Start   
221 Start   
222 End

I want the based on Start and end output like

The output I'm expecting -

SeqNo   Gap    Index
20    Start     1
21    End       1
29    Start     2
30    End       2
42    Start     3
43    End       3
49    Start     4
50    Start     4
51    Start     4
52    Start     4
53    Start     4
54    Start     4
55    End       4
220   Start     5
221   Start     5
222   End       5

I want to divide the column by Start and end.

Sabreen Sageer
  • 11
  • 2
  • I think you should use the code/format options when editing your question .... it's hard to tell where there are supposed to be line/record breaks in your example data (and I suspect there probably are) – Craig Apr 21 '23 at 02:04

1 Answers1

2

Using a subquery:

select t.*, (select sum(t1.seqno < t.seqno and t1.gap = "End") from tbl t1) + 1 
from tbl t

See fiddle.

Ajax1234
  • 69,937
  • 8
  • 61
  • 102
  • ```SUM(gap='End') OVER () - SUM(gap='End') OVER (ORDER BY seqno DESC) + 1``` may be more fast then the subquery. – Akina Apr 21 '23 at 05:17