Dense Rank with order by

Question

I have Assignment Table like this

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 which should increase whenever RCD or Company combination changes and it should be order by 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 Dense_Rank function with order by EMPLID , RCD , COMPANY , It provides me Counter but its not in order by effdt.

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

Order by EFFDT , Gives incremental counter 1 ... 6

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

Kindly help me to find out what I am missing.

Dense_rank will keep same number as long as your order by data is same ... so as you seen, it gives from 1 to 6 if you dense rank by date ... Just use 'dense_rank() over (order by company) as counter', and at end of your select query order by EFFDT .... it will give you what you want — Veljko89, Nov 03 '16 at 07:53
What is the desired output if the next line in the table (for `EMPLID=100, EFFDT=5/1/2000`) has the combination `RCD=0, COMPANY=xyz`? Should the counter return to 1, or should it be assigned a new value of 4? — , Nov 03 '16 at 12:59
@Veljko89 - I tried having Order by EFFDT in last , still it will it gives Rank 1 to (0,ggg) and 2 to (0,xyz) as it order by emplid ,rcd and company first , not effdt. Only difference putting Order by EFFDT will make that result shown will be ordered by EFFDT. So Rank 2 will be shown First and 1 later. — Bhushan, Nov 04 '16 at 01:29
@mathguy - I got another requirement where I will need rank as 4 , if RCD and COMPANY Combination repeats.Can you please help me with solution. — Bhushan, Feb 20 '17 at 04:30
On this site, you can't ask specific people to help. You can ask the community. If the question is different, but related to this one, the best thing is to ask a *new* question, in which you refer to this one and state that you need to ask a different question, related to the old one. It may be best to "copy and paste" the input data into the new question, and explain the new requirement in detail (I didn't understand it from what you wrote) and desired output from the test data. Good luck! — , Feb 20 '17 at 13:09

Serg · Answer 1 · 2016-11-03T10:42:08.820

3

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 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) COUNTER
FROM grps

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

edited Nov 03 '16 at 10:42

answered Nov 03 '16 at 10:13

Serg

22,285
5
21
48

Thanks for your response. two step process may work but I can not use it because sample problem is simple but program where i need to accommodate it , will not work with 2 steps – Bhushan Nov 04 '16 at 01:31
@bhushan - why will a "two-step" solution not work? I believe you *think* it won't work; you are probably wrong, but let's figure it out. In any case, Serg's solution would be the right one, but for one thing - if you have the combination 0, xyz again at a later date, it will give it a new counter value, it will not recognize it's still the old 1. (Which is why I asked you in a comment and you clarified - to have the requirement fully understood first.) – Nov 04 '16 at 02:25

score 2 · Accepted Answer · answered Nov 04 '16 at 02:50

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 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

score 0 · Answer 3 · answered Nov 03 '16 at 10:29

0

I think you're 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);

answered Nov 03 '16 at 10:29

Kacper

4,798
2
19
34

I tried having Order by EFFDT in last , still it will it gives Rank 1 to (0,ggg) and 2 to (0,xyz) as it order by emplid ,rcd and company first , not effdt. Only difference putting Order by EFFDT will make that result shown will be ordered by EFFDT. So Rank 2 will be shown First and 1 later. – Bhushan Nov 04 '16 at 01:32

Dense Rank with order by

3 Answers3