CODEWITHSUNDEEP
sqloracle-databasecount
user1683776
user1683776

Reputation: 61

Create a Column to Group Rows and Count Sequentially

I have a Column 1 which in this case is the same value repeating through all the rows. Column 2 has non distinct rows in which values may repeat. I want to create a column named Group to show that the rows belong to this group in seqential order (Ordered by Column 2). Basically, I need to create an output that looks similar to this:

Column 1   | Column 2   | Group
--------------------------------
100        | AA         | 1
100        | AA         | 1
100        | AA         | 1 
100        | BB         | 2
100        | BB         | 2
100        | CC         | 3
100        | DD         | 4
100        | DD         | 4

Upvotes: 1

Views: 1033

Answers (3)

Robert
Robert

Reputation: 25753

Try this code:

with list as
(select distinct col2 from tab order by 1),
      listnum as
(select rownum, col2 from list)

select * 
from listnum l
join tab t on t.col2=l.col2;

SQL Fiddle DEMO

Links about code I used:

Upvotes: 1

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

You could use the dense_rank function in oracle. 

select col1,
       col2,
       dense_rank () over (partition by col1
                         order by col2) group_column
  from test_table;

      COL1 CO GROUP_COLUMN
---------- -- ------------
       100 AA            1
       100 AA            1
       100 AA            1
       100 BB            2
       100 BB            2
       100 CC            3
       100 DD            4
       100 DD            4

You could use "order by col1, col2" to verify the results.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269913

The function that you want is dense_rank not rank:

select column1, column2,
       dense_rank() over (order by column2)
from t

DENSE_RANK() produces numbers from 1 to the number of groups, with no gaps. Also, this should not have a partition, for what you are doing.

Upvotes: 2

Related Questions