How to use filtering in ROW_NUMBER() TSQL

user_11156131 0
Viewed 46

I have a source table as following

SN name status
1 Sam OnHold
2 Sam OnHold
3 Sam NotOnHold
4 Sam OnHold
5 Alan OnHold
6 Alan NotOnHold
7 Alan OnHold
8 Elis NotOnHold
9 Elis NotOnHold
10 Elis NotOnhold

I want to use a ROW_NUMBER() function for rows with OnHold status grouped by name. My desired output is following

SN name status rank
1 Sam OnHold 1
2 Sam OnHold 2
3 Sam NotOnHold NULL
4 Sam OnHold 3
5 Alan OnHold 1
6 Alan NotOnHold NULL
7 Alan OnHold 2
8 Elis NotOnHold NULL
9 Elis NotOnHold NULL
10 Elis NotOnhold NULL

I can reach to the desired outcome by doing following

declare @t1 TABLE (SN int, name varchar(10), status varchar(10))
INSERT INTO @t1
Select SN, name,status
from
(
VALUES 
(1, 'Sam', 'OnHold'), 
(2, 'Sam', 'OnHold'), 
(3, 'Sam', 'NotOnHold'), 
(4, 'Sam', 'OnHold'), 
(5, 'Alan', 'OnHold'), 
(6, 'Alan', 'NotOnHold'), 
(7, 'Alan', 'OnHold'), 
(8, 'Elis', 'NotOnHold'), 
(9, 'Elis', 'NotOnHold'), 
(10, 'Elis', 'NotOnhold')
) t (SN, name,status)


select * from

(select SN, name, status, ROW_NUMBER() OVER (PARTITION BY name ORDER BY SN) as rank from @t1 where   status='OnHold'
union all
select SN, name, status, null as rank from @t1 where status<>'OnHold') a
ORDER BY SN

But is there a better optimized way to reach to the desired outcome at once without having to use Union all at all.

I tried the following but it does not give me what I want

select SN, name, status, CASE when status='OnHold' then ROW_NUMBER() OVER (PARTITION BY name ORDER BY SN) else NULL end as RANK from @t1 order by SN

Thanking you in advance.

tsql sql-server
1 Answers

You could use a CASE expression:

SELECT SN,
       [name],
       [Status],
       CASE [status] WHEN 'OnHold' THEN RANK() OVER (PARTITION BY [name], [status] ORDER BY SN) END AS Rank
FROM @t1 t1
ORDER BY SN;
user_20299830
Related