'Row_number skip values

I have a table like this:

1

The idea were to count only when I have "Include" at column include_appt, when it finds NULL, it should skip set is as "NULL" or "0" and on next found "Include" back to counting where it stopped.

The screenshot above I was almost able to do it but unfortunately the count didn't reset on next value.

PS: I can't use over partition because I have to keep the order by id ASC

sqlsql-serverconditional-statementsrow-number


Solution 1:[1]

If you are trying to prevent row numbers being added for NULL/0 values, why not try a query like this instead?

SELECT
    row_num AS id,
    include_appt,
    ROW_NUMBER() OVER
    (
        ORDER BY (SELECT 0)
    ) AS row_num2
FROM C
WHERE ISNULL(C.include_appt, 0) <> 0
ORDER BY row_num

I would recommend reconsidering the column names/aliases you want to have displayed in your final result to avoid confusion, but the above should effectively do what you are wanting.

Solution 2:[2]

You need a PARTITION BY clause

SELECT
    row_num AS id,
    include_appt,
    CASE WHEN include_appt IS NULL
      THEN 0
      ELSE
        ROW_NUMBER() OVER (PARTITION BY include_appt ORDER BY (SELECT 0))
      END AS row_num2
FROM C
ORDER BY row_num

Solution 3:[3]

SELECT id, include_appt,
CASE WHEN include_appt IS NULL THEN 0
  ELSE ROW_NUMBER() OVER (PARTITION BY include_appt ORDER BY id ASC)
  END AS row_num 
FROM #1 ORDER BY id asc

Solution 4:[4]

This can be easily done with a partition by include_appt as in another answer below, yet after playing around with the query plans I've decided that it is still worthwhile to consider this slightly different approach which might offer a performance boost. I believe the benefit is gained by being able to use the clustered index without involving a sort on the flag column:

select id, flag,
    case when flag is not null
        then row_number() over (order by id)
               - count(case when flag is null then 1 end) over (order by id)
        else 0 end /* count up the skips */ as new_rn
from T
order by id

Examples (including a "reset" behavior): https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=c9f4c187c494d2a402e43a3b24924581

Performance comparison: https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=719f7bd26135ab498d11c786f1b1b28b

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 cdbullard
Solution 2 Charlieface
Solution 3 Ramkumar Sambandam
Solution 4

Related Questions