Dense rank with order

Question

I have an assignment table

EMPLID | RCD | COMPANY | EFFDT | SALARY --------------------------------------------------- 100 | 0 | xyz | 1/1/2000 | 1000 100 | 0 | xyz | 1/15/2000 | 1100 100 | 0 | xyz | 1/31/2000 | 1200 100 | 0 | ggg | 2/15/2000 | 1500 100 | 1 | abc | 3/1/2000 | 2000 100 | 1 | abc | 4/1/2000 | 2100 

I need a counter that should increment whenever the combination of RCD or Company changes, and this should be an order from effdt.

 EMPLID | RCD | COMPANY | EFFDT | SALARY | COUNTER -------|-----|---------|---------------|-------------|---------- 100 | 0 | xyz | 1/1/2000 | 1000 | 1 100 | 0 | xyz | 1/15/2000 | 1100 | 1 100 | 0 | xyz | 1/31/2000 | 1200 | 1 100 | 0 | ggg | 2/15/2000 | 1500 | 2 100 | 1 | abc | 3/1/2000 | 2000 | 3 100 | 1 | abc | 4/1/2000 | 2100 | 3 

I tried the Dense_Rank function with the order by EMPLID, RCD, COMPANY, it provides me with a counter, but not in order with effdt.

 SELECT EMPLID,RCD,COMPANY,EFFDT, DENSE_RANK() over (order by EMPLID , RCD , COMPANY) AS COUNTER FROM ASSIGNMENT ; 

Order by EFFDT, gives an incremental counter 1 ... 6

 SELECT EMPLID,RCD,COMPANY,EFFDT, DENSE_RANK() over (order by EFFDT) AS COUNTER FROM ASSIGNMENT; 

Please help me find out what I am missing.

Answer 1

This should work - with the clarification that the combination of rcd and company must maintain the same “counter”, even if it appears for several consecutive periods. I added a few lines to the test data to make sure I get the correct result.

Like Serg solutions (which answer another question), the solution makes one pass over the basic data, and then a second pass based on the results of the first pass (everything is in memory, so it should be relatively fast). There is no way around this - it requires two different analytic functions, where each depends on the results of the other, and nested analytic functions are not allowed. (This part of the answer is addressed by the OP comment on Sergey's answer.)

 with test_data ( emplid, rcd, company, effdt, salary ) as ( select 100, 0, 'xyz', to_date('1/1/2000' , 'mm/dd/yyyy'), 1000 from dual union all select 100, 0, 'xyz', to_date('1/15/2000', 'mm/dd/yyyy'), 1100 from dual union all select 100, 0, 'xyz', to_date('1/31/2000', 'mm/dd/yyyy'), 1200 from dual union all select 100, 0, 'ggg', to_date('2/15/2000', 'mm/dd/yyyy'), 1500 from dual union all select 100, 1, 'abc', to_date('3/1/2000' , 'mm/dd/yyyy'), 2000 from dual union all select 100, 1, 'abc', to_date('4/1/2000' , 'mm/dd/yyyy'), 2100 from dual union all select 100, 0, 'xyz', to_date('5/1/2000' , 'mm/dd/yyyy'), 2200 from dual union all select 100, 1, 'ggg', to_date('8/15/2000', 'mm/dd/yyyy'), 2300 from dual ) -- end of test data; the actual solution (SQL query) begins below this line select emplid, rcd, company, effdt, salary, dense_rank() over (partition by emplid order by min_dt) as counter from ( select emplid, rcd, company, effdt, salary, min(effdt) over (partition by emplid, rcd, company) as min_dt from test_data ) order by effdt -- ORDER BY is optional ; EMPLID RCD COM EFFDT SALARY COUNTER ---------- ---------- --- ------------------- ---------- ---------- 100 0 xyz 2000-01-01 00:00:00 1000 1 100 0 xyz 2000-01-15 00:00:00 1100 1 100 0 xyz 2000-01-31 00:00:00 1200 1 100 0 ggg 2000-02-15 00:00:00 1500 2 100 1 abc 2000-03-01 00:00:00 2000 3 100 1 abc 2000-04-01 00:00:00 2100 3 100 0 xyz 2000-05-01 00:00:00 2200 1 100 1 ggg 2000-08-15 00:00:00 2300 4 8 rows selected 

Answer 2

Try lag

 WITH flagged AS ( SELECT *, CASE WHEN LAG(RCD) OVER(PARTITION BY EMPLID ORDER BY EFFDT) = RCD AND LAG(COMPANY) OVER(PARTITION BY EMPLID ORDER BY EFFDT) = COMPANY THEN 0 ELSE 1 END strtFlag FROM tbl ) SELECT EMPLID, RCD, COMPANY, EFFDT, SALARY, SUM(strtFlag) OVER(PARTITION BY EMPLID ORDER BY EFFDT) COUNTER FROM flagged 

alternatively, with the DENSE_RANK () group

 WITH grps AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY EMPLID ORDER BY EFFDT) - ROW_NUMBER() OVER(PARTITION BY EMPLID, RCD, COMPANY ORDER BY EFFDT) grp FROM tbl ) SELECT EMPLID, RCD, COMPANY, EFFDT, SALARY , DENSE_RANK() OVER(PARTITION BY EMPLID ORDER BY grp) COUNTER FROM grps 

In any case, two steps are needed to get dense numbering.

Answer 3

I think you are looking for:

 SELECT EMPLID,RCD,COMPANY,EFFDT, DENSE_RANK() over (order by EMPLID , RCD , COMPANY) AS COUNTER FROM (select * from ASSIGNMENT order by EFFDT); 

or

 SELECT EMPLID,RCD,COMPANY,EFFDT, DENSE_RANK() over (order by EMPLID , RCD , COMPANY) AS COUNTER FROM (select * from ASSIGNMENT order by EMPLID , RCD , COMPANY, EFFDT);