sqloraclerow-number

# Creating second sequence based on argument; Oracle

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?

Solution

• 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