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?

CodePudding user response：

use a case statement in the select clause of your query to create the second sequence.

select seq_no,
       part_no,
       row_number() over(partition by part_no order by seq_no) as RowNumber,
       case when level > 2 then null else RowNumber end as SecondRowNumber,
       level
from table1

CodePudding user response：

You can create a CTE with the wnated secnd 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