create sequence of numbers on grouped column in Oracle

Question

Consider below table with column a,b,c.

a   b   c
3   4   5
3   4   5
6   4   1
1   1   8
1   1   8
1   1   0
1   1   0

I need a select statement to get below output. i.e. increment column 'rn' based on group of column a,b,c.

a   b   c   rn
3   4   5   1
3   4   5   1
6   4   1   2
1   1   8   3
1   1   8   3
1   1   0   4
1   1   0   4

Answer 1

You can use the DENSE_RANK analytic function to get a unique ID for each combination of A, B, and C. Just note that if a new value is inserted into the table, the IDs of each combination of A, B, and C will shift and may not be the same.

Query

WITH
    my_table (a, b, c)
    AS
        (SELECT 3, 4, 5 FROM DUAL
         UNION ALL
         SELECT 3, 4, 5 FROM DUAL
         UNION ALL
         SELECT 6, 4, 1 FROM DUAL
         UNION ALL
         SELECT 1, 1, 8 FROM DUAL
         UNION ALL
         SELECT 1, 1, 8 FROM DUAL
         UNION ALL
         SELECT 1, 1, 0 FROM DUAL
         UNION ALL
         SELECT 1, 1, 0 FROM DUAL)
SELECT t.*, DENSE_RANK () OVER (ORDER BY b desc, c desc, a) as rn
  FROM my_table t;

Result

   A    B    C    RN
____ ____ ____ _____
   3    4    5     1
   3    4    5     1
   6    4    1     2
   1    1    8     3
   1    1    8     3
   1    1    0     4
   1    1    0     4