CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle-data-integrator
DatabaseAdministrator1
DatabaseAdministrator1

Reputation: 11

Auto Increment for each Duplicate Value in Oracle

I am using Oracle Database Integrator (ODI) to create an interface for a new table.

What I am trying to do is to incrementally increase a number for each duplicate appearance of a value in another column. For example, for the table:

Person_ID  | ...
32         | ...
45         | ...
32         | ...
67         | ...
45         | ...
45         | ...

The interface would output the following in the target table:

Person_ID | Sequence_Number | ...  
32        |       1         | ...
45        |       1         | ...
32        |       2         | ...
67        |       1         | ...
45        |       2         | ...
45        |       3         | ...

I've tried to do this by entering various SQL queries into the Implementation text editor in Mapping Properties, but it seems like I cannot actually make it incrementally increase.

Any help would be greatly appreciated!

Upvotes: 1

Views: 2254

Answers (1)

void
void

Reputation: 7890

you can use row_number(), or rank() with over(partition by personId order by personId)

select personId,
       row_number() over(partition by personId order by personId) Sequence_Number
from your_table

Edit: if you want the result sorted as exactly like the desired output you mentioned, you need to do order by rownum twice to guarantee the desired sort:

select personId,
       row_number() over(partition by personId order by rownum) Sequence_Number
from your_table
order by rownum

Below is a check for above queries:

SQL> create table your_table (personId int);

Table created.

SQL> insert all
  2      into your_table values(32)
  3      into your_table values(45)
  4      into your_table values(32)
  5      into your_table values(67)
  6      into your_table values(45)
  7      into your_table values(45)
  8      select * from dual;

6 rows created.

SQL> commit;

Commit complete.

SQL> select personId,
  2         row_number() over(partition by personId order by rownum) Sequence_Number
  3  from your_table;

  PERSONID SEQUENCE_NUMBER
---------- ---------------
        32               1
        32               2
        45               1
        45               2
        45               3
        67               1

6 rows selected.

SQL> select personId,
  2         row_number() over(partition by personId order by rownum) Sequence_Number
  3  from your_table
  4  order by rownum;

  PERSONID SEQUENCE_NUMBER
---------- ---------------
        32               1
        45               1
        32               2
        67               1
        45               2
        45               3

6 rows selected.

SQL> drop table your_table;

Table dropped.

SQL>

Upvotes: 3

Related Questions