Query returning an ascending group number

Question

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.

Answer 1

try DENSE_RANK.

select DENSE_RANK() OVER(partition by col2 order by col1, col2) as GRPNUM, 
       COL1, COL2
from ....

For the given data this works but i don't know if it'll work for the real dataset.

Answer 2

Hi Dougman,

This will work:

SQL> WITH qry AS (
  2       select 'A' col1, 1 col2 from dual union all
  3       select 'A' col1, 2 col2 from dual union all
  4       select 'B' col1, 1 col2 from dual union all
  5       select 'B' col1, 2 col2 from dual union all
  6       select 'C' col1, 1 col2 from dual union all
  7       select 'C' col1, 2 col2 from dual
  8  )
  9  SELECT dense_rank() over (ORDER BY col1) grpnum,
 10         col1,
 11         col2
 12    FROM qry
 13   ORDER BY col1, col2;

    GRPNUM COL1       COL2
---------- ---- ----------
         1 A             1
         1 A             2
         2 B             1
         2 B             2
         3 C             1
         3 C             2

Answer 3

I don't have Oracle handy, so there may be some dialect issues, but how about something like this: Create a temporary table from "distinct col1", assign sequence numbers to it, and then join against this. Something like:

create sequence groupnumber;

create temp_group (grpnum int, col1 varchar(20), col2 varchar(20));

insert into temp_group
select next_val('groupnumber'), col1
from
(select distinct col1 from incoming
order by col1);

select grpnum, col1, col2
from incoming
join temp_group using (col1)
order by col1, col2;

(You could probably use the oracle rownumber instead of the sequence.)