CODEWITHSUNDEEP
sqlsql-servercountnumberssequence
Ekm
Ekm

Reputation: 11

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

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....

Upvotes: 0

Views: 4539

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

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;

Upvotes: 2

Related Questions