Row_number skip values-CodePudding

I have a table like this:

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

CodePudding user response：

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.

CodePudding user response：

row_number() over (order by id)
  - sum(case when include_appt is null then 1 end) over (order by id)