select version from v$instance;
10.2.0.5.0
Here is my example query:
WITH A AS (
select 40 as id,'708' as loc,'10-108' as act,14.5 as per from dual
union select 40,'708','10-308',14.5 from dual
union select 40,'708','10-708',14.5 from dual
union select 40,'708','10-108',10.5 from dual
union select 40,'708','10-308',10.5 from dual
union select 40,'708','10-708',10.5 from dual
)
select id,loc,act,per
,SUBSTR(act,4,3) as aloc
,CASE WHEN SUBSTR(act,4,3) = loc THEN 0 ELSE 1 END as "Case"
,row_number() over (partition by id,loc order by per desc,
CASE WHEN SUBSTR(act,4,3) = loc THEN 0 ELSE 1 END) AS Row_Num
,dense_rank() over (partition by id,loc order by per desc
,CASE WHEN SUBSTR(act,4,3) = loc THEN 0 ELSE 1 END
) as D_Rank
from A;
These are the results I would expect:
ID LOC ACT PER ALOC Case ROW_NUM D_RANK
---------- --- ------ ---------- ---- ---------- ---------- ----------
40 708 10-708 14.5 708 0 1 1
40 708 10-108 14.5 108 1 2 1
40 708 10-308 14.5 308 1 3 1
40 708 10-708 10.5 708 0 4 2
40 708 10-108 10.5 108 1 5 2
40 708 10-308 10.5 308 1 6 2
However, these are the results I receive:
ID LOC ACT PER ALOC Case ROW_NUM D_RANK
---------- --- ------ ---------- ---- ---------- ---------- ----------
40 708 10-708 14.5 708 0 1 1
40 708 10-108 14.5 108 1 2 2
40 708 10-308 14.5 308 1 3 2
40 708 10-708 10.5 708 0 4 3
40 708 10-108 10.5 108 1 5 4
40 708 10-308 10.5 308 1 6 4
Since CASE WHEN SUBSTR(act,4,3) = loc THEN 0 ELSE 1 END is not in the partition, why does DENSE_RANK() increment? ROW_NUMBER() behaves correctly with this CASE statement in its ORDER BY, but DENSE_RANK() does not seem to.
I believe I can solve my problem by removing CASE WHEN SUBSTR(act,4,3) = loc THEN 0 ELSE 1 END from my DENSE_RANK() ORDER BY, however, academically, I still cannot understand the above behavior. Is it a bug in DENSE_RANK or is it by design?