Reset Row_Number() when seeing 1 in another column-CodePudding

Say I have table like this.

| Date       | Condition |
|------------|-----------|
| 2021-12-24 | 0         |
| 2021-12-24 | 0         |
| 2021-12-24 | 0         |
| 2021-12-24 | 0         |
| 2021-12-24 | 1         |
| 2021-12-24 | 0         |
| 2021-12-24 | 0         |
| 2021-12-25 | 0         |
| 2021-12-25 | 0         |
| 2021-12-25 | 0         |

I want to add row_number to it, but when it see isCon = 1 or the date changed, I want it to reset to 1, so it would be like this.

| Date       | Condition | row_number |
|------------|-----------|------------|
| 2021-12-24 | 0         | 1          |
| 2021-12-24 | 0         | 2          |
| 2021-12-24 | 0         | 3          |
| 2021-12-24 | 0         | 4          |
| 2021-12-24 | 1         | 1          |
| 2021-12-24 | 0         | 2          |
| 2021-12-24 | 0         | 3          |
| 2021-12-25 | 0         | 1          |
| 2021-12-25 | 0         | 2          |
| 2021-12-25 | 0         | 3          |

I tried

    WITH cte AS
(
    SELECT *
        ,ROW_NUMBER() OVER (PARTITION BY [isCon],[date],[grp] ORDER BY [date]) AS rn
    FROM (select t.*,
             (  
                row_number() over (order by date) -
                row_number() over (partition by isCon,date order by date)
              ) as grp
            from @myTablevariable t
          ) t
    
)
SELECT * FROM cte;

It's close but not quite right. Row_number reset to 1 no matter it sees 0 or 1, but I want it to reset only when seeing 1.

| Date       | Condition | row_number |
|------------|-----------|------------|
| 2021-12-24 | 0         | 1          |
| 2021-12-24 | 0         | 2          |
| 2021-12-24 | 0         | 3          |
| 2021-12-24 | 0         | 4          |
| 2021-12-24 | 1         | 1          |
| 2021-12-24 | 0         | 1          |
| 2021-12-24 | 0         | 2          |
| 2021-12-25 | 0         | 1          |
| 2021-12-25 | 0         | 2          |
| 2021-12-25 | 0         | 3          |

Please note that I am on SQL server 2008 and it is not possible for me to upgrade. So anything like LAG() function or SUM(column1) OVER (ORDER BY) is not viable for me.

CodePudding user response：

As I mentioned in the comments, you need a column to ORDER BY here; if you don't have one then what you are after is impossible without adding said column (end of story).

Assuming you do have a column you can ORDER BY this is just a gaps and island problem:

WITH CTE AS(
    SELECT YourAlwaysAscendingColumn,
           [date],
           Condition,
           ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY YourAlwaysAscendingColumn) - 
           ROW_NUMBER() OVER (PARTITION BY [Date], Condition ORDER BY YourAlwaysAscendingColumn) AS Grp
    FROM dbo.YourTable)
SELECT [date],
       Condition,
       ROW_NUMBER() OVER (PARTITION BY [Date], Grp ORDER BY YourAlwaysAscendingColumn) AS RowNumber
FROM CTE
ORDER BY [date],
         YourAlwaysAscendingColumn;

db<>fiddle

CodePudding user response：

Perhaps like this?

DECLARE
    @TestData TABLE
    (
        SomeDate DATE,
        Condition INT
    );

INSERT INTO @TestData
VALUES
('20211224', 0),
('20211224', 0),
('20211224', 0),
('20211224', 0),
('20211224', 0),
('20211224', 1),
('20211224', 0),
('20211224', 0),
('20211225', 0),
('20211225', 0),
('20211225', 0)

SELECT
    SomeDate,
    Condition,
    ROW_NUMBER() OVER (PARTITION BY SomeDate, Condition ORDER BY (SELECT 0))
FROM
    @TestData;

The (SELECT 0) can be any constant, e.g. (SELECT 'anything')