Conditional RowNumber

Question

I am trying to put conditional numbering depending on a result from RowNum column.

When the RowNum is 1 I would like to have new column with brand new increment by 1.

In the picture in column RoomNum 5 should be replaced by 2, 9 by 3m 13 by 4, etc. What am I doing wrong in this query?

SELECT  CASE 
        WHEN rownum < 2
    THEN
    Row_number() OVER (
        PARTITION BY Scheme ORDER BY Scheme ASC
        )  
    ELSE NULL
END AS RoomNum,

CASE 
    WHEN rownum > 1
        THEN NULL
    ELSE scheme
    END AS Scheme
,RowNum

Answer 1

You need to partition by whether or not RoomNm is NULL. The resulting value would also have a CASE:

select (case when roomnum is not null
             then row_number() over (partition by scheme, (case when roomnum is not null then 1 else 0 end)
                                     order by roomnum
                                    )
        end) as RoomNum