CODEWITHSUNDEEP
sqloracle-databaseanalytic-functions
Benoit
Benoit

Reputation: 79185

SQL analytic functions - How to assign number to a partition?

I am using Oracle.

Suppose I am having a table with this sample, random, content:

columnA     | columnB  | content
--------------------------------
AfBkxZ      | 292      | a
LDglkK      | 181      | b
AfBkxZ      | 51       | c
AfBkxZ      | 315      | d
LDglkK      | 808      | e
Cee89g      | 1        | f

I would like to have a view inside which I have a unique number for each value in columnA, and record number inside rows for which columnA has that value.

Wanted result based on the sample data above:

Group_number | Record_number | columnB | content
------------------------------------------------
1            | 2             | 292     | a             (1.2)
3            | 1             | 181     | b             (3.1)
1            | 1             | 51      | c             (1.1)
1            | 3             | 315     | d             (1.3)
3            | 2             | 808     | e             (3.2)
2            | 1             | 1       | f             (2.1)

I can obtain record_number with row_number() over (partition by columnA order by columnB asc).

How do I obtain the group_number which is actually a friendly sequenced alias for the old columnA?

Thank you.

Upvotes: 3

Views: 1034

Answers (1)

Andomar
Andomar

Reputation: 238086

You could use dense_rank to number columnA:

dense_rank() over (order by columnA)

Upvotes: 6

Related Questions