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.
I want to get:
seq role_name sequence 1 B 10 2 C 15 2 C 15 3 A 25 3 A 30
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