CODEWITHSUNDEEP
sqloracle-databaserow-number
user15746603
user15746603

Reputation:

Window function with where condition (conditional row_number())

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

Upvotes: 2

Views: 4677

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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

Upvotes: 1

Related Questions