how to count sequential column and each one is counted before rows in sql

Question

I am using MSSQL for my application and I need to count sequential number status predecessor line.

Table is like this,

+------+------+
|Number|Status|
|     1|     N|
|     2|     G|
|     3|     G|
|     4|     N|
|     5|     N|
|     6|     G|
|     7|     G|
|     8|     N|
+------+------+

I suppose result set as follows ex.

+------+------+-----+
|Number|Status|Count|
|     1|     N| 1   |
|     2|     G| 1   |
|     3|     G| 2   |
|     4|     N| 1   |
|     5|     N| 2   |
|     6|     G| 1   |   
|     7|     G| 2   |   
|     8|     G| 3   |
+------+------+-----+

I couldn't use cursor for performance of query. it is worst case options....

what is the logic of count ? ? ? Please provide more detail to understand your problem — Khurram Ali, Feb 13 '15 at 20:32
possible duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) — Tab Alleman, Feb 13 '15 at 20:32

score 2 · Accepted Answer · answered Feb 13 '15 at 22:05

You need to identify groups of consecutive "N" and "G" values. I like to approach this with a difference of row numbers. Then you can use row_number() to enumerate the rows:

select t.number, t.status,
       row_number() over (partition by status, grp order by number) as seqnum
from (select t.*,
             (row_number() over (order by number) -
              row_number() over (partition by status order by number
             ) as grp
      from table t
     ) t;

how to count sequential column and each one is counted before rows in sql

1 Answers1