When you use the within group clause with dense_rank, the database:
- Sorts the rows according to your order by
- Inspects values you passed to dense_rank(v1, v2, ...)
- Determines where in the ordered set your values belong
- Assigns a value based on the dense_rank algorithm (ties get the same value, no gaps in the numbers)
So you get the value three because if you sort the rows by sal desc, comm desc your data looks like:
EID NAME SALARY COMM_PCT
100 Steve 24000
102 Lex 17000 0.8
101 Neena 17000 0.6 <===== 17000, 0.6 fits here
145 John 14000 0.4
17000, 0.6 slots in the 3rd position. There's no ties before it. So you get the value three.
This also works with rank(). But you'll get slightly different results, because after rows with the same values it starts from the current row number:
with rws as (
select 100 eid, 'Steve' name, 24000 salary, null comm_pct from dual union all
select 101 eid, 'Neena' name, 17000 salary, 0.6 comm_pct from dual union all
select 102 eid, 'Lex' name, 17000 salary, 0.8 comm_pct from dual union all
select 145 eid, 'John' name, 14000 salary, 0.4 comm_pct from dual
)
select dense_rank(17000,0.6) within group (order by salary desc,comm_pct desc) dr_sal_comm,
dense_rank(14000) within group (order by salary desc) dr_sal,
rank(14000) within group (order by salary desc) rk_sal
from rws r;
DR_SAL_COMM DR_SAL RK_SAL
3 3 4
with rws as (
select 100 eid, 'Steve' name, 24000 salary, null comm_pct from dual union all
select 101 eid, 'Neena' name, 17000 salary, 0.6 comm_pct from dual union all
select 102 eid, 'Lex' name, 17000 salary, 0.8 comm_pct from dual union all
select 145 eid, 'John' name, 14000 salary, 0.4 comm_pct from dual
)
select r.*,
dense_rank() over (order by salary desc,comm_pct desc) dr_sal_comm,
dense_rank() over (order by salary desc) dr_sal,
rank() over (order by salary desc) rk_sal
from rws r;
EID NAME SALARY COMM_PCT DR_SAL_COMM DR_SAL RK_SAL
100 Steve 24000 1 1 1
102 Lex 17000 0.8 2 2 2
101 Neena 17000 0.6 3 2 2
145 John 14000 0.4 4 3 4