Search code examples
sqlsql-serverrow-number

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


I have a problem with the answered question from this question

Can you find the solution? It's wrong result rn Column if the data chr like this one

WITH chr AS (
    SELECT 581827 AS custno, '2012-11-08 08:38:00.000' AS moddate, 'EMSZC14' AS who UNION ALL
    SELECT 581827, '2012-11-08 09:14:18.000', 'EMSZC49' UNION ALL
    SELECT 581827, '2012-11-08 09:17:35.000', 'EMSZC14' UNION ALL  -- It is Fail
    SELECT 581827, '2012-11-08 09:17:35.000', 'EMSZC14'   -- It is Fail
),
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;

Wrong Result :

custno  moddate who rn
581827  2012-11-08 08:38:00.000 EMSZC14 1
581827  2012-11-08 09:14:18.000 EMSZC49 1
581827  2012-11-08 09:17:35.000 EMSZC14 2
581827  2012-11-08 09:17:35.000 EMSZC14 3

Expected Result :

custno  moddate who rn
581827  2012-11-08 08:38:00.000 EMSZC14 1
581827  2012-11-08 09:14:18.000 EMSZC49 1
581827  2012-11-08 09:17:35.000 EMSZC14 1
581827  2012-11-08 09:17:35.000 EMSZC14 2

Solution

  • You just need to add who in the final row_number as follows:

    WITH chr AS (
        SELECT 581827 AS custno, '2012-11-08 08:38:00.000' AS moddate, 'EMSZC14' AS who UNION ALL
        SELECT 581827, '2012-11-08 09:14:18.000', 'EMSZC49' UNION ALL
        SELECT 581827, '2012-11-08 09:17:35.000', 'EMSZC14' UNION ALL  -- It is Fail
        SELECT 581827, '2012-11-08 09:17:35.000', 'EMSZC14'   -- It is Fail
    ),
    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, who, rn1 - rn2 ORDER BY moddate) rn
    FROM cte
    ORDER BY
        custno,
        moddate;
    

    Db<>fiddle