How to reorder a DENSE_RANK column considering multiple columns in Oracle?

Question

I have a query following which have a DENSE_RANK and ROW_NUMBER columns:

WITH CTE AS(
    SELECT
    A.SL_NO, 
    A.ACC_NO, 
    A.ACC_NAME
    DENSE_RANK() OVER(ORDER BY A.ACC_NO, A.ACC_NAME) DRN,
    ROW_NUMBER() OVER(PARTITION BY A.ACC_NO, A.ACC_NAME ORDER BY A.SL_NO) RN
    FROM TEST_TBL A 
)
SELECT * 
FROM CTE A
ORDER BY A.SL_NO;

The query results in:

SL_NO   ACC_NO  ACC_NAME DRN RN
1        234      UNIP    3   1
2        234      UNIP    3   2
3        234      UNIP    3   3
4        256      PURP    4   1
5        256      PURP    4   2
6        289      KFAR    5   1
7        210      FHAS    2   1
8        210      FHAS    2   2
9        210      FHAS    2   3
10       110      PURP    1   1
11       110      PURP    1   2
12       110      PURP    1   3
13       110      PURP    1   4

But do wanna order the DRN column like this (The rank must be according to the acc_no and acc_name columns):

SL_NO   ACC_NO  ACC_NAME DRN RN
1        234      UNIP    1   1
2        234      UNIP    1   2
3        234      UNIP    1   3
4        256      PURP    2   1
5        256      PURP    2   2
6        289      KFAR    3   1
7        210      FHAS    4   1
8        210      FHAS    4   2
9        210      FHAS    4   3
10       110      PURP    5   1
11       110      PURP    5   2
12       110      PURP    5   3
13       110      PURP    5   4

Need suggestions to achieve it with or without using DENSE_RANK. Thanks in advance.

user_1863229 · Answer

It looks like you just want the DRN column to be a dense rank as ordered by the SL_NO column. We can subquery once, and arbitrary take the minimum SL_NO value per each account, and then use dense rank afterwards:

WITH cte AS (
    SELECT
        SL_NO, 
        ACC_NO, 
        ACC_NAME,
        ROW_NUMBER() OVER(PARTITION BY ACC_NO, ACC_NAME ORDER BY SL_NO) RN,
        MIN(SL_NO) OVER (PARTITION BY ACC_NO, ACC_NAME) AS SL_NO_MIN
    FROM TEST_TBL A
)

SELECT
    SL_NO, 
    ACC_NO, 
    ACC_NAME,
    DENSE_RANK() OVER (ORDER BY SL_NO_MIN) AS DRN,
    RN
FROM cte
ORDER BY
    SL_NO;

How to reorder a DENSE_RANK column considering multiple columns in Oracle?

1 Answers

Demo