Window function with where condition (conditional row_number())

Question

I have the below clause in a select statement

ROW_NUMBER() OVER(
    PARTITION BY pc
    ORDER BY
        a DESC, b DESC
) r

How can I apply that function only to the rows which fulfill a certain condition but without filtering the selection at the end in a where clause? .

Sample data:

PC	A	B
pc1	a1	b1
pc1	a2	b2
pc1	a3	b3

Desired output (the condition in this case would be where a2!='a2'):

PC	A	B	R
pc1	a1	b1	1
pc1	a2	b2	null
pc1	a3	b3	2

EDIT: I've tried the below, but it does not start from 1 but from the whole rownum count.

CASE
    WHEN condition THEN
        ROW_NUMBER() OVER(
            PARTITION BY pc
            ORDER BY
                a, b
        )
END r1

Answer 1

Use row_number() within a "case when" statement with a second case statement in the "partition by" as below:

(Case condition when true then ROW_NUMBER() OVER(
    PARTITION BY (case condition when true then pc end)
    ORDER BY
        a DESC, b DESC
) 
end)r

Example:

 create table sampledata(PC varchar(10),    A varchar(10),  B varchar(10));
 insert into sampledata values('pc1',   'a1',   'b1');
 insert into sampledata values('pc1',   'a2',   'b2');
 insert into sampledata values('pc1',   'a3',   'b3');

Query:

 select *,(Case when A<>'a2'  then ROW_NUMBER() OVER(
     PARTITION BY (case when A<>'a2' then pc end)
     ORDER BY   a , b DESC
 ) 
 end)r
 from sampledata order by a, b desc

Output:

pc	a	b	r
pc1	a1	b1	1
pc1	a2	b2	null
pc1	a3	b3	2

db<fiddle here

If condition is A<>'a1' then

Query:

 select *,(Case when A<>'a1'  then ROW_NUMBER() OVER(
     PARTITION BY (case when A<>'a1' then pc end)
     ORDER BY   a , b DESC
 ) 
 end)r
 from sampledata order by a, b desc

Output:

pc	a	b	r
pc1	a1	b1	null
pc1	a2	b2	1
pc1	a3	b3	2

db<fiddle here