How to skip NULL (or 0) when using Row_Number?

I need to return the sequential number of a row based on values in ColValue. I need to do it until NULL occurs in a row, then start to count from the beginning. My original table is in the left picture, what I need is on the right (column RowNr):

[

I tried various combinations of ROW_NUMBER but in vain. Instead of NULL I can also set 0 or other value but the issue is how to start counting from the beginning right after this value.

One of my tries is here:

    SELECT journalized_id, ColValue,
        CASE WHEN ColValue IS NULL THEN NULL ELSE ROW_NUMBER() OVER(PARTITION BY CASE WHEN ColValue IS NULL THEN 1 ELSE 0 END ORDER BY ColValue) END AS RowNr
    FROM TableX

Do you have any idea how to solve it?

Answer

@mustafa00, I believe @GordonLinoff’s answer is essentially correct. The main issue is that he has the colvalue field performing two roles which are incompatible. An understandable problem given how the question was presented. You need to find another field (or set of fields), to play one of those roles. Without that the question cannot be answered.

SELECT
    SubQuery.SomeOtherField,
    SubQuery.Journalized_Id,
    SubQuery.ColValue,
    SubQuery.Grp, --So you can see what it outputs
    CASE
        WHEN SubQuery.ColValue IS NOT NULL
        THEN ROW_NUMBER() OVER (PARTITION BY SubQuery.Grp ORDER BY SubQuery.SomeOtherField)
    END - (CASE WHEN SubQuery.Grp = 0 THEN 0 ELSE 1 END) AS RowNr 
          --A minor correction to Gordon's code to ensure each sequence starts at 1.
FROM
    (
    SELECT
        t.SomeOtherField,
        t.Journalized_Id,
        t.ColValue,
        SUM(CASE WHEN t.ColValue IS NULL THEN 1 ELSE 0 END)
            OVER (ORDER BY t.SomeOtherField) AS Grp
    FROM
        MyTable t
    ) SubQuery

The SubQuery is creating groups for each set of records requiring it’s own number sequence starting at 1. It does this by counting the number of NULL values in ColValue which come before the current record. It can’t do this unless we can give the query some means of understanding which records are really before the others, and to do that you need SomeOtherField (or set of fields) by which to order the records.

It is likely that there is some primary key to your table that can play the role of SomeOtherField.

This solution is demonstrated with your data plus SomeOtherField in this SQL Fiddle.