Creating second sequence based on argument; Oracle

Creating second sequence based on argument; Oracle

Problem Description:

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 part_no is listed:

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:

Does anyone have an idea how to solve this?

Solution – 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"

fiddle