Have Geek Brain

Request returning the upstream group number

I have a query like below that has groups (COL1) and values ​​for that group (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 result of this query looks like this:

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

I need a query that will return an ordered number increasing for each group (COL1). It seems like there would be an easy way to accomplish this (perhaps with analytics), but for some reason it eludes 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.

+2


a source to share


3 answers


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
+3


a source


try DENSE_RANK.

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


For dataset this works, but I don't know if it will work for a real dataset.

+1


a source


I don't have a handy Oracle so there might be some dialect issues, but how about something like this: Create a temp table from "different col1", assign sequence numbers to it, and then join that. Sort of:

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 can probably use oracle roaming instead of sequence.)

0


a source






More articles:


All Articles