How to use DENSE_RANK and order by aditional column?

Question

I am trying to use dense_rank to get element order for instance:

I have table seq_test with data:

create table seq_test (sequence number, role_name varchar2(20));

insert into seq_test values (10, 'B');
insert into seq_test values (20, 'A');

select DENSE_RANK() over (order by role_name) as seq
     , role_name
     , sequence
    from seq_test
    order by sequence

after I run the code above, I've got :

SEQ   ROLE_NAME SEQUENCE
2     B         10
1     A         20

I want to achieve:

SEQ   ROLE_NAME SEQUENCE
1     B         10
2     A         20

So DENSE_RANK() function use its own order defined in function definition I need to order the SEQ column by sequence column.

Update:

I want to get:

seq role_name sequence
1   B         10
2   C         15
2   C         15
3   A         25
3   A         30

If you need `SEQ` ordered by `SEQUENCE`, then why is the dense rank function ordered by `ROLE_NAME`? It should be ordered by `SEQUENCE`. Sorry, but the question doesn't make a lot of sense. — , Dec 16 '21 at 22:53
wouldn't your dense rank be `(order by role_name desc)`? and why dense_rank... row_number() seems like it would work given data I don't see a reason for dense_rank given sample data. — xQbert, Dec 16 '21 at 23:18

score 2 · Answer 1 · answered Dec 16 '21 at 23:00

2

Try this query:

select DENSE_RANK() over (order by SEQUENCE) as seq
 , role_name
 , sequence
from seq_test;

Result:

seq role_name sequence
  1 B               10
  2 A               20

answered Dec 16 '21 at 23:00

S_sauden

302
2
10

Thanks, but I need to use role_name and get the same "SEQ" for duplicate role_names – Goku Dec 17 '21 at 10:54

Barbaros Özhan · Accepted Answer · 2021-12-17T12:17:25.547

Since the conventional ORDER BY clause is performed after the analytic processing. So the ORDER BY clause of the SELECT statement will always take precedence over that comes from the order of the rows as they are processed by an analytic function.

In your case ORDER BY sequence overrides ORDER BY role_name which comes from the analytic function .

Btw, what you need depending on the last comment might be resolved by adding an extra MIN() analytic function such as

SELECT DENSE_RANK() OVER (ORDER BY seq) AS seq, role_name, sequence
  FROM
  (
   SELECT MIN(sequence) OVER (PARTITION BY role_name ) AS seq,
          role_name, sequence
     FROM seq_test
  ) t

Demo

How to use DENSE_RANK and order by aditional column?

2 Answers2