Transact-SQL - number rows until condition met

Question

I'm trying to generate the numbers in the "x" column considering the values in field "eq", in a way that it should assign a number for every record until it meets the value "1", and the next row should reset and start counting again. I've tried with row_number, but the problem is that I only have ones and zeros in the column I need to evaluate, and the cases I've seen using row_number were using growing values in a column. Also tried with rank, but I haven't managed to make it work.

nInd    Fecha       Tipo    @Inicio     @contador_I  @Final     @contador_F eq  x
1       18/03/2002  I       18/03/2002  1            null       null        0   1
2       20/07/2002  F       18/03/2002  1            20/07/2002 1           1   2
3       19/08/2002  I       19/08/2002  2            20/07/2002 1           0   1
4       21/12/2002  F       19/08/2002  2            21/12/2002 2           1   2
5       17/03/2003  I       17/03/2003  3            21/12/2002 2           0   1
6       01/04/2003  I       17/03/2003  4            21/12/2002 2           0   2
7       07/04/2003  I       17/03/2003  5            21/12/2002 2           0   3
8       02/06/2003  F       17/03/2003  5            02/06/2003 3           0   4
9       31/07/2003  F       17/03/2003  5            31/07/2003 4           0   5
10      31/08/2003  F       17/03/2003  5            31/08/2003 5           1   6
11      01/09/2005  I       01/09/2005  6            31/08/2003 5           0   1
12      05/09/2005  I       01/09/2005  7            31/08/2003 5           0   2
13      31/12/2005  F       01/09/2005  7            31/12/2005 6           0   3
14      14/01/2006  F       01/09/2005  7            14/01/2006 7           1   4

Answer 1

There is another solution available:

select 
  nind, eq, row_number() over (partition by s order by s) 
from (
  select 
    nind, eq, coalesce((
      select sum(eq) +1 from mytable pre where pre.nInd < mytable.nInd)
    ,1) s --this is the sum of eq!
  from mytable) g

The inner subquery creates groups sequentially for each occurrence of 1 in eq. Then we can use row_number() over partition to get our counter.

Here is an example using Sql Server

Answer 2

I have two answers here. One is based off of the ROW_NUMBER() and the other is based off of what appears to be your index (nInd). I wasn't sure if there would be a gap in your index so I made the ROW_NUMBER() as well.

My table format was as follows -

myIndex int identity(1,1) NOT NULL number int NOT NULL

First one is ROW_NUMBER()...

WITH rn AS (SELECT *, ROW_NUMBER() OVER (ORDER BY myIndex) AS rn, COUNT(*) AS max 
                  FROM counting c GROUP BY c.myIndex, c.number)
,cte (myIndex, number, level, row) AS (

    SELECT r.myIndex, r.number, 1, r.rn + 1 FROM rn r WHERE r.rn = 1
    UNION ALL
    SELECT r1.myIndex, r1.number, 
                       CASE WHEN r1.number = 0 AND r2.number = 1 THEN 1
                                                                 ELSE c.level + 1
                       END,
                       row + 1
    FROM cte c 
        JOIN rn r1 
            ON c.row = r1.rn
        JOIN rn r2
            ON c.row - 1 = r2.rn
    )

SELECT c.myIndex, c.number, c.level FROM cte c OPTION (MAXRECURSION 0);

Now the index...

WITH cte (myIndex, number, level) AS (

    SELECT c.myIndex + 1, c.number, 1 FROM counting c WHERE c.myIndex = 1
    UNION ALL
    SELECT c1.myIndex + 1, c1.number, 
                           CASE WHEN c1.number = 0 AND c2.number = 1    THEN 1
                                                                        ELSE c.level + 1
                           END
    FROM cte c 
        JOIN counting c1
            ON c.myIndex = c1.myIndex
        JOIN counting c2
            ON c.myIndex - 1 = c2.myIndex
    )

SELECT c.myIndex - 1 AS myIndex, c.number, c.level FROM cte c OPTION (MAXRECURSION 0);