Query returning an ascending group number
- by Dougman
I have a query like below that has groups (COL1) and that group's values (COL2).
select col1,
col2
from (select 'A' col1, 1 col2 from dual union all
select 'A' col1, 2 col2 from dual union all
select 'B' col1, 1 col2 from dual union all
select 'B' col1, 2 col2 from dual union all
select 'C' col1, 1 col2 from dual union all
select 'C' col1, 2 col2 from dual
)
order by col1,
col2;
The output of this query looks like:
COL1 COL2
---- ----
A 1
A 2
B 1
B 2
C 1
C 2
What I need is a query that will return an ordered number increasing for each different group (COL1). It seems like there would be a simple way to accomplish this (maybe with analytics) but for some reason it is escaping me.
GRPNUM COL1 COL2
------ ---- ----
1 A 1
1 A 2
2 B 1
2 B 2
3 C 1
3 C 2
I am running Oracle 10gR2.
