CODEWITHSUNDEEP
sqloracle-database
stoniel2
stoniel2

Reputation: 93

Return column with running sequence number Oracle

My simple query returns data like this:

SELECT column1, column2 FROM table1

COLUMN1   COLUMN2
-------   -------
CA         A
CA         B
CB         C
CB         D

I want to return column3 with these values (for same COLUMN1 value, I want to return same sequence number):

COLUMN3
-------
1
1
2
2

Upvotes: 1

Views: 102

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

You can use analytic function DENSE_RANK.

SELECT column1, 
       column2,
       DENSE_RANK() OVER(ORDER BY column1) as "column3"
 FROM table1

See the following for some examples - oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions.php#dense_rank

Upvotes: 2

vivek
vivek

Reputation: 35

Try this query,

Select column1, column2, 
       dense_rank() over (order by column1) as column3 
from table1;

Upvotes: 2

Related Questions