Assign a group ID

Question

Here are my data

unic_key    rnk fg_new_session
1011854     1   1
1012047     2   0
1012055     3   0
1012056     4   1
1012057     5   0
1018928     6   0
1018932     7   0
825651      56  1
826034      57  0
826048      58  0

I would like to add a 4th column which contains the previous unic_key where fg_new_session = 1 order by rnk

The output would be:

unic_key    rnk fg_new_session  4thCol
1011854     1   1               1011854
1012047     2   0               1011854
1012055     3   0               1011854
1012056     4   1               1012056
1012057     5   0               1012056
1018928     6   0               1012056
1018932     7   0               1012056
825651      56  1               825651
826034      57  0               825651
826048      58  0               825651

I wanted to use analytic function to do it, but i didnt manage to do it. I guess join is the only way to do that.

Answer 1

You can use window function first_value and sum like this:

select t.*,
    first_value(unic_key) over (
        partition by grp order by fg_new_session desc,
            rnk
        ) the_new_column
from (
    select t.*,
        sum(fg_new_session) over (
            order by rnk
            ) grp
    from t
    ) t;

sum here is used to create groups and then first_value to get the first value in the group (in the order of increasing rank and as per the order by clause, it will have max fg_new_session in the group)