Use Row_Number() in case statement in sql server

Question

I Left Joined 2 table Table A and Table B and the output is below which is correct

select a.id,b.OS_ID from TableA left join TableB
on a.id=b.id

What I want to achieve is replacing NULL with 51 and 52.

If I run this

Declare @OSID int
set @OSID = (select max(os_ID) from OS_Master)

It will give me output as 50, Then I want to increment by 1 for the next entry and replace the NULL value

ID OS_ID
1 1
1 14
1 NULL
1 NULL

If I do this:

Declare @OSID int
set @OSID = (select max(os_ID) from OS_Master)
select ROW_NUMBER() over (order by os_ID) + @OSID from OS_Master

It works fine, ROW_NUMBER() starts from 51, But If I incorporate it with the join like

select a.ID,case when b.OS_ID is null then (select ROW_NUMBER() over (order by b.os_ID) + @OSID ) 
else b.OS_ID END    from TABLEA a
left JOIN TABLEB  b
on a.ID=b.ID

It shows 51 for both the NULL records, How to get 51,52 ans so on in my query

You mention about output, NULL but you didn't show us how the data looks like. — Squirrel
– Squirrel, Commented Jan 28, 2020 at 13:33

Gordon Linoff · Accepted Answer · 2020-01-28 13:32:02Z

1

Is this what you want?

select a.id,
       coalesce(b.OS_ID, om.max_os_id + row_number() over (partition by b.os_id)) as os_id
from TableA left join
     TableB
     on a.id = b.id cross join
     (select max(os_ID)  as max_os_id from OS_Master) om;

This increments the value based on the maximum value, using row_number().

answered Jan 28, 2020 at 13:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use Row_Number() in case statement in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related