# Creating second sequence based on argument; Oracle

## Creating second sequence based on argument; Oracle

Contents

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:

SEQ_NOPART_NORowNumberLEVEL
110PRD10111
120PRD10122
130PRD10133
140PRD10144
150PRD10151
160PRD10162
110PRD10211
120PRD10222
130PRD10232
140PRD10241
110PRD10311
120PRD10321

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_NOPART_NORowNumberSecondRowNumberLEVEL
110PRD101111
120PRD101222
130PRD10133
140PRD10144
150PRD101531
160PRD101642
110PRD102111
120PRD102222
130PRD102332
140PRD102441
110PRD103111
120PRD103221

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"
``````
SEQ_NOPART_NOROWNUMBER_SECONDROWNUMBERLEVEL
110PRD101111
120PRD101222
130PRD1013null3
140PRD1014null4
150PRD101531
160PRD101642
110PRD102111
120PRD102222
130PRD102332
140PRD102441
110PRD103111
120PRD103221

fiddle

Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.