Query returning an ascending group number

Posted by Dougman on Stack Overflow See other posts from Stack Overflow or by Dougman
Published on 2010-05-10T15:12:57Z Indexed on 2010/05/10 15:44 UTC
Read the original article Hit count: 140

Filed under:
|

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.

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about sql