CODEWITHSUNDEEP
sqlmysqldata-partitioningrownum
Sabreen Sageer
Sabreen Sageer

Reputation: 11

How can I create an index based on values from another column in SQL?

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.

Upvotes: 1

Views: 79

Answers (1)

Ajax1234
Ajax1234

Reputation: 71471

Using a subquery:

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

See fiddle.

Upvotes: 2

Related Questions