A table
column1 column2 column3 column4
340 9870 abc mno
338 9870 abc mno
337 3456 abc mno
336 3456 abc mno
335 3456 abc mno
334 3456 abc mno
332 3456 abc mno
331 3456 abc mno
252 2132 abc mno
251 2132 abc mno
249 2132 abc mno
Expected output
column1 column2 column3 column4 column5
340 9870 abc mno 1
338 9870 abc mno 9
337 3456 abc mno 8
336 3456 abc mno 7
335 3456 abc mno 6
334 3456 abc mno 5
332 3456 abc mno 4
331 3456 abc mno 3
252 2132 abc mno 2
251 2132 abc mno 1
249 2132 abc mno 1
Is it possible to generate row number within partition by column3 and column4, order by on column2, column1 when values in column1 are in a sequence, but with an exception : 252 and 331 must be considered sequential as a special case while generating row number.
Code below works without the exception
select t1.*,
row_number() over (partition by column3,column4, column1 - num order by column2,column1 ) as column5
from (select t.*,row_number() over (partition by column3,column4 order by column2,column1) as num
from table1 t
) t1
order by column2 desc,column1 desc
I think this is a gaps-and-islands problem. Assuming there are no values between 252 and 331, then you can use lag() to determine where a grouping begins, a cumulative sum, and your final result is a row_number() on the grouping.
I also don't see a role for column2:
select t.*,
row_number() over (partition by column3, column4, grp order by column1) as column5
from (select t.*,
sum(case when prev_column1 is null or
(prev_column1 <> column1 - 1 and
prev_column1 <> 252 and
column1 <> 331
)
then 1 else 0
end) over (partition by column3, column4 order by column1) as grp
from (select t.*,
lag(column1) over (partition by column3, column4 order by column1) as prev_column1
from t
) t
) t;
Here is a db<>fiddle.
Note that your sample data also has a break at 333, which this picks up. However, this shows how you can address the question that you asked.