CODEWITHSUNDEEP
sql
tablecloth26
tablecloth26

Reputation: 49

Resetting the row number for when a value changes

I'm fairly new to using ROW_NUMBER() in SQL and I'm in the situation where I want the row number to start from 1 whenever the margin gets changed.

The situation is slightly convoluted since I want the row number to reset irrespective if the margin was the same value before. Below is what I'm trying to achieve:

enter image description here

Any help on the above would be greatly appreciated!

Upvotes: 0

Views: 2217

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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;

Upvotes: 2

Related Questions