Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Arun.

Asked: June 20, 2017 - 11:59 am UTC

Last updated: June 22, 2017 - 10:04 am UTC

Version: 11G

Viewed 1000+ times

You Asked

Below is the the employee table,

EID NAME SALARY COMM_PCT
100 Steven 24000
101 Neena 17000 0.6
102 Lex 17000 0.8
145 John 14000 0.4

If I do the below select to employee table I get the output as 3.

SELECT DENSE_RANK(17000,0.6) WITHIN GROUP (ORDER BY salary DESC,COMM_PCT DESC) "Dense Rank"
FROM OE.employees;

so my assumption is database calculates the weight-age between 0.6 and 0.8 in comm_pct column and decides the rank... Is this correct?

and Chris said...

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     

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 20, 2017 - 5:33 pm UTC


flipping the values

Rajeshwaran, Jeyabal, June 21, 2017 - 8:10 am UTC

thanks, was able to understand how the dense_rank is 3 for this dense_rank(17000,0.6).

but when I flip the values like dense_rank(0.6,17000) the outcome is 5, could you help us to understand, why it is 5 here?

demo@ORA12C> with datas as (
  2  select 100 as eid,'Steven' as name, 24000 as salary, to_number(null) as comm_pct from dual union all
  3  select 101 as eid,'Neena' as name, 17000 as salary, 0.6 as comm_pct from dual union all
  4  select 102 as eid,'Lex' as name, 17000 as salary, 0.8 as comm_pct from dual union all
  5  select 145 as eid,'John' as name, 14000 as salary, 0.4 as comm_pct from dual )
  6  select dense_rank(17000,0.6) within group (order by salary desc,comm_pct desc) dr ,
  7         dense_rank(0.6,17000) within group (order by salary desc,comm_pct desc) dr2
  8  from datas ;

        DR        DR2
---------- ----------
         3          5

demo@ORA12C>

Chris Saxon
June 21, 2017 - 10:05 am UTC

See Tobias' reply below.

@Rajeshwaran

Tobias, June 21, 2017 - 8:37 am UTC

If you switch the values in the DENSE_RANK, you also need to switch the order of the fields in the ORDER BY clause (in line 7). Then it works!

with datas as (
select 100 as eid,'Steven' as name, 24000 as salary, to_number(null) as comm_pct from dual union all
select 101 as eid,'Neena' as name, 17000 as salary, 0.6 as comm_pct from dual union all
select 102 as eid,'Lex' as name, 17000 as salary, 0.8 as comm_pct from dual union all
select 145 as eid,'John' as name, 14000 as salary, 0.4 as comm_pct from dual )
select dense_rank(17000,0.6) within group (order by salary desc,comm_pct desc) dr ,
       dense_rank(0.6,17000) within group (order by comm_pct desc, salary desc) dr2
from datas ;

Chris Saxon
June 21, 2017 - 10:04 am UTC

Yep, thanks Tobias

the logic behind the value 5 | To Chirs and Tobia's.

Rajeshwaran, Jeyabal, June 21, 2017 - 10:43 am UTC

given this spec,

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


it explains clearly how dense_rank within group works.

my question is " when I flip the values like dense_rank(0.6,17000) the outcome is 5 " - how is it getting assigned with 5? what was the logic behind that? help me to understand how it got 5 here?

Hope i am clear with my question now.

demo@ORA12C> with datas as (
  2  select 100 as eid,'Steven' as name, 24000 as salary, to_number(null) as comm_pct from dual union all
  3  select 101 as eid,'Neena' as name, 17000 as salary, 0.6 as comm_pct from dual union all
  4  select 102 as eid,'Lex' as name, 17000 as salary, 0.8 as comm_pct from dual union all
  5  select 145 as eid,'John' as name, 14000 as salary, 0.4 as comm_pct from dual )
  6  select dense_rank(17000,0.6) within group (order by salary desc,comm_pct desc) dr ,
  7         dense_rank(0.6,17000) within group (order by salary desc,comm_pct desc) dr2
  8  from datas ;

        DR        DR2
---------- ----------
         3          5

Chris Saxon
June 22, 2017 - 10:04 am UTC

As Tobias says below, this is because the arguments to dense_rank are interpreted in the same order as the columns in the order by. So you're saying 0.6 is a salary!

@Rajeshwaran

Tobias, June 21, 2017 - 11:51 am UTC

In your SQL, 0.6 is interpretated as the salary, which is sorted descending. The four lines in the result set have salaries 24000, 17000, 17000 and 14000. So 0.6 gets Dense_Rank 5.

Un-Identified Records shouldn't be null?

Praveen Ray, June 21, 2017 - 1:36 pm UTC

WHY - it isn't NULL in place of 5, when this produces NULL with analytics for the same case?

My Bad

Praveen Ray, June 21, 2017 - 1:58 pm UTC

<<snippet from reference>>
•As an aggregate function, DENSE_RANK calculates the dense rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification.

Here "hypothetical row" clarifies everything...

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.