Hello
I have a situation where I need to generate a row_number for a sequence of lines in a contract. However I only want to generate row_number for lines that meet specific criteria and I need them to remain gap free.
I know that we don't have anything like row_number(case when some_criteria then 1 end) over(partition....), but I want to simulate that type of functionality.
I could of course use an inline view to filter the lines I'm interested in with a where clause and then join back to that, but I was hoping to avoid multiple passes of the underlying table.
I've arrived at using COUNT with an order by and then a case statement around that to make sure I'm only outputting the count on relevant rows.
[code]
with src as
( select 1 id, to_date('01/01/2020','dd/mm/yyyy') dt, 'VALID' status from dual union all
select 1 id, to_date('01/01/2020','dd/mm/yyyy') dt, 'NOT VALID' status from dual union all
select 1 id, to_date('03/01/2020','dd/mm/yyyy') dt, 'NOT VALID' status from dual union all
select 1 id, to_date('04/01/2020','dd/mm/yyyy') dt, 'VALID' status from dual union all
select 2 id, to_date('01/01/2020','dd/mm/yyyy') dt, 'NOT VALID' status from dual union all
select 2 id, to_date('02/01/2020','dd/mm/yyyy') dt, 'NOT VALID' status from dual union all
select 2 id, to_date('03/01/2020','dd/mm/yyyy') dt, 'NOT VALID' status from dual union all
select 2 id, to_date('04/01/2020','dd/mm/yyyy') dt, 'VALID' status from dual
)
select
id,
dt,
status,
row_number() over(partition by id order by dt) rn,
row_number() over(partition by id, status order by dt) rn_status,
count(case when status = 'VALID' THEN 1 END) over(partition by id order by dt) ct,
CASE
WHEN status ='VALID' THEN
count(case when status = 'VALID' THEN 1 END) over(partition by id order by dt)
END ct_case
--row_number(case when status = 'VALID' THEN 1 END) over(partition by id order by dt)
from
src
[/code]
but I'm guessing this isn't the only option. Are there any alternatives that might offer advantages over COUNT?
Oh and I'm on 11.2.0.4 (yes I know, it's old!)
Cheers
David