Resetting Row Number According to Record Data Change

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.

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;

T-SQL Reset Row number when field changes, including repeated instances

You can do what you want using the "difference of row numbers" approach:

select c.*,
       row_number() over (partition by userid, outcome, seqnum_u - seqnum_uo
                          order by dateofcall
                         ) as rnk
from (select c.*,
             row_number() over (partition by userid order by dateofcall) as seqnum_u,
             row_number() over (partition by userid, outcome order by dateofcall) as seqnum_uo
      from Calls c
     ) c
order by UserID, DateOfCall;

This logic is tricky the first time you see it (hey, maybe even the third or fifth time too). I advise you to run the inner query so you can see why the difference identifies groups of adjacent status values. Then the row_number() in the outer query make more sense.

Conditionally reset row_number

You can use a conditional cumulative sum to further partition the rows by the event field:

with cte as (
  select id, time, event,
         sum(case when event = 'y' then 1 else 0 end) over (partition by id order by time) as group_id
    from tbl
)
select id, time, event,
       row_number() over (partition by id, group_id order by time) as rank
  from cte

---

Related Topics