Creating second sequence based on argument; Oracle

Question

I have the following question. I have generated the following RowNumber column by usage of the rownumber() function and the over(paritation by clause. The counting starts with '1' every time a new Number is listed:

SEQ_NO	Number	RowNumber	LEVEL
110	PRD101	1	1
120	PRD101	2	2
130	PRD101	3	3
140	PRD101	4	4
150	PRD101	5	1
160	PRD101	6	2
110	PRD102	1	1
120	PRD102	2	2
130	PRD102	3	2
140	PRD102	4	1
110	PRD103	1	1
120	PRD103	2	1

The query is kind of like this:

select seq_no, part_no, row_number() over(partition by part_no order by seq_no) as RowNumber, level 
from table1

The point is that I would like to create a second sequence which does not fill any value in for rows where levels > 2
The second sequence is also paritated by the part_no

The table would result like:

SEQ_NO	Number	RowNumber	SecondRowNumber	LEVEL
110	PRD101	1	1	1
120	PRD101	2	2	2
130	PRD101	3		3
140	PRD101	4		4
150	PRD101	5	3	1
160	PRD101	6	4	2
110	PRD102	1	1	1
120	PRD102	2	2	2
130	PRD102	3	3	2
140	PRD102	4	4	1
110	PRD103	1	1	1
120	PRD103	2	2	1

Does anyone have an idea how to solve this?

Answer 1

You can create a CTE with the wanted second row numbers and join it

WITH CTE as (
  select "SEQ_NO", "PART_NO"
  , row_number() over(partition by "PART_NO" order by "SEQ_NO") as RowNumber, "LEVEL"  
from table1
  WHERE "LEVEL"  <= 2
  )
select table1."SEQ_NO", table1."PART_NO"
  , row_number() over(partition by table1."PART_NO" order by table1."SEQ_NO") as RowNumber_
  , CTE.RowNumber as secondRowNumber
  , table1."LEVEL" 
from table1 LEFT JOIN CTE ON table1."SEQ_NO"  = CTE."SEQ_NO"  AND table1."PART_NO" = CTE."PART_NO"

SEQ_NO	PART_NO	ROWNUMBER_	SECONDROWNUMBER	LEVEL
110	PRD101	1	1	1
120	PRD101	2	2	2
130	PRD101	3	null	3
140	PRD101	4	null	4
150	PRD101	5	3	1
160	PRD101	6	4	2
110	PRD102	1	1	1
120	PRD102	2	2	2
130	PRD102	3	3	2
140	PRD102	4	4	1
110	PRD103	1	1	1
120	PRD103	2	2	1

fiddle