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

Question

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

Please keep in mind that people in this community mostly interest with text based sample data rather than images. — Barbaros Özhan, Sep 22 '19 at 08:56
Yes, please paste the sample data as text into your request. — Thorsten Kettner, Sep 22 '19 at 10:10

score 1 · Accepted Answer · answered Sep 22 '19 at 09:53

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;

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

1 Answers1