Window function with where condition (conditional row_number())

Admin 1
Viewed 1124

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
row-number oracle sql
2 Answers

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

user_86516010

I suspect you want a conditional sum:

SUM(CASE WHEN <condition> THEN 1 ELSE 0 END) OVER (
    PARTITION BY pc
    ORDER BY a DESC, b DESC
) as r

If you want NULL for the non-matching values:

(CASE WHEN <condition>
      THEN SUM(CASE WHEN <condition> THEN 1 ELSE 0 END) OVER (
                PARTITION BY pc
                ORDER BY a DESC, b DESC
              )
 END) as r
user_11440350
Related