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

You should tag your question with the database you are using. — Gordon Linoff, Jul 23 '15 at 14:44

score 20 · Accepted Answer · answered Jul 23 '15 at 14:47

20

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

answered Jul 23 '15 at 14:47

Gordon Linoff

1,242,037
58
646
786

RoomNm in language above the code should be Roomnum – Rick Pack Oct 03 '22 at 09:40
can you help me understand what the 'case when' does within the 'partition by' clause? – Rick Pack Oct 03 '22 at 09:49
1

It does the same thing as it does elsewhere. The purpose is to separate the NULL values. – Gordon Linoff Oct 05 '22 at 19:30

score 0 · Answer 2 · edited May 19 '17 at 16:59

--I think you can work around this way 
--I have little different scenario but hope logic help

declare @ts table
    (WK_DAYS int
    ,DAY_NAME VARCHAR(12)
    ,WORKTYPE   varchar(50)
    ,WK_HOURS   int
    ,workday int)

insert into @ts (WK_DAYS,DAY_NAME,WORKTYPE,WK_HOURS) values (1,'MON','SICK',8)
insert into @ts (WK_DAYS,DAY_NAME,WORKTYPE,WK_HOURS) values (2,'TUE','LABOR',8)
insert into @ts (WK_DAYS,DAY_NAME,WORKTYPE,WK_HOURS) values (3,'WED','LABOR',8)
insert into @ts (WK_DAYS,DAY_NAME,WORKTYPE,WK_HOURS) values (4,'THU','VACATION',8)
insert into @ts (WK_DAYS,DAY_NAME,WORKTYPE,WK_HOURS) values (5,'FRI','LABOR',8)
insert into @ts (WK_DAYS,DAY_NAME,WORKTYPE,WK_HOURS) values (6,'SAT','LABOR',8)
insert into @ts (WK_DAYS,DAY_NAME,WORKTYPE,WK_HOURS) values (7,'SUN','LABOR',8)

SELECT * FROM @TS

SELECT
    X.*
    ,Y.WORKING_DAY
FROM
(SELECT * FROM @ts)X
    LEFT JOIN
(SELECT *,ROW_NUMBER()OVER (ORDER BY WK_DAYS) AS WORKING_DAY FROM @ts WHERE WORKTYPE NOT IN ('SICK','VACATION','SATURDAY','SUNDAY'))Y
ON X.WK_DAYS = Y.WK_DAYS

Can you explain a little more about how you are solving the problem and what the original poster is doing wrong? — polka, May 19 '17 at 17:24

Conditional RowNumber

2 Answers2