window function based on condition

Question

Input table is as follows:

column1         column2         column3         column4
230             8979            abc             mno
228             8979            abc             mno
227             6578            abc             mno
226             6578            abc             mno
225             6578            abc             mno
224             6578            abc             mno
223             6578            abc             mno
222             6578            def             pqr

Would it be possible to generate the output column, where row number is generated based on condition - row number for partition by column2, column3 and column4, order by on column1 only if values in column1 are in sequence. If not in sequence, then new set of row number has to be generated.

Output to be generated

column1         column2         column3         column4       output
230             8979            abc             mno           1       
228             8979            abc             mno           1
227             6578            abc             mno           5
226             6578            abc             mno           4
225             6578            abc             mno           3
224             6578            abc             mno           2
223             6578            abc             mno           1
222             6578            def             pqr           1

Olga Romantsova · Answer 1 · 2020-08-07T16:53:03.987

You can use row_number to get number for every partition by column2,column3,column4 and then get group for sequence (column1 - num):

  select X.column1,X.column2,X.column3,X.column4,
           row_number() over (partition by column2,column3,column4, column1 - num
                              order by column1
                             ) as needed_number
    from (select t.*,
                 row_number() over (partition by column2,column3,column4 order by column1) as num
               
          from Table t
         ) X
  order by column1 desc

score 0 · Answer 2 · answered Aug 07 '20 at 16:42

Use the below query and remove the unwanted columns

SQLFiddle

SELECT T.*, CASE WHEN COLUMN1 <> PREV_NUM + 1 THEN 1 ELSE RANK() OVER(PARTITION BY COLUMN2, COLUMN3, COLUMN4 ORDER BY COLUMN1, COLUMN2 DESC, COLUMN3, COLUMN4) END AS NEW_RANK FROM (
SELECT A.*, LEAD(COLUMN1) OVER(PARTITION BY COLUMN2, COLUMN3, COLUMN4 ORDER BY COLUMN1 DESC, COLUMN2 DESC) PREV_NUM
FROM TABLE1 A ORDER BY 1 DESC) T ORDER BY 1 DESC;

window function based on condition

2 Answers2