[SOLVED] Generate order number based on a column value with reference to other columns

Generate order number based on a column value with reference to other columns

This is question in Oracle views.I have a table with Emp_id,Start_Period and Key. Sample data is given in Descending order of start period with 201909 on top. Need to generate a column named Key_order. (Finally I am planning to create a view with all 4 columns.)

With the sample data as shown. In the sorted list with Start_period what ever comes in first position with number 1 and then on, when the Key changes order has to increment by one.

That is in row 1 and 2 key is same and order is 1. In row 3 SCD changed to ABC so order has to increment by 1 so order value is 2. 4th position key changes and order becomes 3.

See in 7th and 8th position value is same so order remains 6 for both. I am trying to do this inside a view. Tried RANK() but it is sorting column Key and giving order based on that. Please help.Sample Data

Solution

Set a one in each line that has a different key than the line before. Use LAG for this. Then build a running total of these ones with SUM OVER.

select
  emp_id, start_period, key,
  sum(chg) over (partition by emp_id order by start_period desc) as key_order
from
(
  select
    emp_id, start_period, key,
    case when key = lag(key) over (partition by emp_id order by start_period desc) 
         then 0 else 1 end as chg
  from mytable
)
order by emp_id, start_period desc;