You Asked

Hi ,

I am would like generate number sequence as first column for my query output. So thought using dense_rank() over ( partition by column order by column desc) and am getting same rank for rows but I could get using row_number () over the correct.

Why I am getting same rank when I use dense_rank in my query?

Note: column which I am using is account number which include alpa numeric.

Can experts explain please?

and Chris said...

Dense_rank and row_number only produce different results if there's duplicates in your order by column(s):

with rws as (
  select chr(level+64) x from dual connect by level <= 5
)
  select x, 
         row_number() over (order by x) rn,
         dense_rank() over (order by x) dr
  from rws;

X  RN  DR  
A  1   1   
B  2   2   
C  3   3   
D  4   4   
E  5   5  
  
with rws as (
  select chr(level+64) x from dual connect by level <= 5
  union all
  select 'B' x from dual
)
  select x, 
         row_number() over (order by x) rn,
         dense_rank() over (order by x) dr
  from rws;

X  RN  DR  
A  1   1   
B  2   2   
B  3   2   
C  4   3   
D  5   4   
E  6   5

Row_number() increments for every row. Dense_rank() increments for every value.

If the values you're ordering by are guaranteed to be unique (within the partition), then both will give you the same results.

Rating

(1 rating)

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

Comments

Dense_rank assigning same rank as row_number

Suresh A, March 14, 2017 - 12:32 pm UTC

Thanks for the info. It's made me to understand my mistake. For number sequence I was using dense_rank with partition by column which I shouldn't instead of dense_rank () over( order by column)..

Thanks
Suresh

More to Explore

Analytics

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

Breadcrumb

Question and Answer