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

Question

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

Can you better describe what `It is Fail` is supposed actually mean here? The query you included is correct for what its original intention was. — Tim Biegeleisen, Feb 04 '21 at 03:25
You need to make your question stand alone, i.e. provide sample data, expected results, and actual results so we can see what isn't working. — Dale K, Feb 04 '21 at 03:29
@DaleK Sample data in that query. Updated my question for expected result and wrong result — dhiewo, Feb 04 '21 at 08:40

score 2 · Accepted Answer · answered Feb 04 '21 at 04:23

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

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

1 Answers1