How to use DENSE_RANK and order by aditional column?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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