Dense Rank with order by

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) COUNTERFROM flagged

alternatively, with DENSE_RANK() of 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) COUNTERFROM grps

Anyway looks like two steps are needed to get dense numbering.

This should work - with the clarification that a combination of rcd and company should keep the same "counter" even if it appears in non-consecutive periods. I added to more rows to the test data to make sure I get the correct result.

Like Serg's solutions (which answer a different question), the solution does one pass over the base data, and then a second pass over the results of the first pass (all in memory, so it should be relatively fast). There's no way around that - this requires two different analytic functions where one depends on the results of the other, and nested analytic functions are not allowed. (This part of the answer addresses a comment by the OP to the Answer by Serg.)

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 lineselect emplid, rcd, company, effdt, salary,       dense_rank() over (partition by emplid order by min_dt) as counterfrom ( 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

I think you're looking for:

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

or

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