T-Sql Row Number Restart After N

T-SQL Row Number Restart after N

Use the modulo operator:

select id, rownumber, 1 + ((rownumber - 1) % 4)
from table t;

If rownumber is not a column, you can also use the row_number() function:

select id, row_number() over (order by id),
       1 + ((row_number() over (order by id) - 1) % 4)
from table t;

How to get ROW_NUMBER to start over (reset) after ROW_NUMBER reaches a certain number

Use modulo arithmetic:

SELECT t.[taskId], tq.name as taskName, 
       1 + (ROW_NUMBER() OVER (PARTITION BY tq.name ORDER BY tq.name) - 1) % 3 AS RowNum_3 
FROM Task t 
ORDER BY tq.name asc;

Resetting the row number for when a value changes

This is a gaps and islands problem, and one approach uses the difference in row numbers method:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CAMPAIGN_NAME ORDER BY DATE DESC) rn1,
              ROW_NUMBER() OVER (PARTITION BY CAMPAIGN_NAME, MARGIN ORDER BY DATE DESC) rn2
    FROM yourTable
)

SELECT CAMPAIGN_NAME, DATE, MARGIN, REVENUE,
       ROW_NUMBER() OVER (PARTITION BY CAMPAIGN_NAME, MARGIN, r1 - r2
                          ORDER BY DATE DESC) AS RN
FROM cte
ORDER BY
    CAMPAIGN_NAME,
    DATE DESC;

Resetting Row number according to record data change

You need to identify the groups of names that occur together. You can do this with a difference of row numbers. Then, use the grp for partitioning the row_number():

select name, date,
       row_number() over (partition by name, grp order by date) as row_num
from (select t.*,
             (row_number() over (order by date) -
              row_number() over (partition by name order by date)
             ) as grp
      from myTBL t
     ) t

For your sample data:

name  date         1st row_number   2nd      Grp
x     2014-01-01         1           1        0
x     2014-01-02         2           2        0
y     2014-01-03         3           1        2
x     2014-01-04         4           3        1

This should give you an idea of how it works.

Reset Row Number on value change, but with repeat values in partition

This is a gaps and islands problem, and we can use the difference in row numbers method here:

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY custno ORDER BY moddate) rn1,
        ROW_NUMBER() OVER (PARTITION BY custno, who ORDER BY moddate) rn2
    FROM chr
)

SELECT custno, moddate, who,
    ROW_NUMBER() OVER (PARTITION BY custno, rn1 - rn2 ORDER BY moddate) rn
FROM cte
ORDER BY
    custno,
    moddate;

Demo

For an explanation of the difference in row number method used here, rn1 is just a time-ordered sequence from 1 onwards, per customer, according to the data you have shown above. The rn2 sequence is partitioned additionally by who. It is the case the difference between rn1 and rn2 will always have the same value, for each customer. It is with this difference that we then take a row number over the entire table to generate the sequence you actually want to see.

---

Related Topics