Usage of Oracle's Row_number () over (order by COL2 ASC) and Row_number () over (PARTITION by COL1 ORDER by COL2)

Transferred from: https://jingyan.baidu.com/article/9989c74604a644f648ecfef3.html

SELECT row_number () Over (PARTITION by TA. process_instance_id ORDER by NVL (ta.status, ' D ') ASC, NVL (ta.finish_date, Ta.start_date) DESC) as Row_index,
Ta.*
From Warn_task Ta;

2.

Row_number () over (PARTITION by COL1 ORDER by COL2) indicates that sorting is based on COL1 within the grouping according to COL2 grouping, and the value computed by this function represents the sequential number of each set of internally ordered (contiguous unique within the group)

The first is divided into successive groups according to PROCESS_INSTANCE_ID;

A similar approach is Dense_rank () and rank over (),

See also: https://www.cnblogs.com/YuyuanNo1/p/7929543.html

Usage of Oracle's Row_number () over (order by COL2 ASC) and Row_number () over (PARTITION by COL1 ORDER by COL2)