Hi All,
I want to assign group ID for each group (run time group ID). I have tried with row_number function but didnt worked for my requiredment. Here is my sample data and my requirement.
| Col1 |
| A |
| A |
| A |
| A |
| A |
| B |
| C |
| D |
| D |
| D |
| D |
| E |
| E |
| E |
| F |
| G |
| G |
| G |
I want to get count of each column value with group ID assign to it. here is my sample output
| Col1 |
Count |
Group ID |
| A |
5 |
1 |
| A |
5 |
1 |
| A |
5 |
1 |
| A |
5 |
1 |
| A |
5 |
1 |
| B |
1 |
2 |
| C |
1 |
3 |
| D |
4 |
4 |
| D |
4 |
4 |
| D |
4 |
4 |
| D |
4 |
4 |
| E |
3 |
5 |
| E |
3 |
5 |
| E |
3 |
5 |
| F |
1 |
6 |
| G |
3 |
7 |
| G |
3 |
7 |
| G |
3 |
7 |
select col1,count(1) over (partition by col1) cnt,
row_number() over ( partition by col1 order by col1 ) grp_id from tbl_test.
Please help me to solve this.