Apply Sequence based on unique_id

I have a table:

table1

unique_id   col_id   val_id
1           100      a
1           101      b
1           102      c
1           103      d

2           106      a
2           106      b
2           104      c
2           103      d

I want to use a sequence to assign it for each unique_id.

I wrote the following which doesn’t seem to work:

SELECT
    my_seq.NEXTVAL over
(
    PARTITION BY a.unique_id ORDER by a.unique_id) AS rec_i,
     a.* FROM table1
a;

The output would be:

unique_id   col_id   val_id   rec_i
1           100      a        123
1           101      b        123
1           102      c        123
1           103      d        123

2           106      a        124
2           106      b        124
2           104      c        124
2           103      d        124

How can I make it work?

Answer

Try this:

-- data preparation
create table tt_table (unique_id   number, col_id   number, val_id varchar2(1));

insert into tt_table values(1,100,'a');
insert into tt_table values(1,101,'b');
insert into tt_table values(1,102,'c');
insert into tt_table values(1,103,'d');

insert into tt_table values(2,106,'a');
insert into tt_table values(2,104,'b');
insert into tt_table values(2,103,'c');
insert into tt_table values(2,103,'d');



-- creating function
CREATE OR REPLACE FUNCTION GET_SEQ RETURN NUMBER AS
BEGIN
    RETURN TEJASH_SEQ.NEXTVAL;
END GET_SEQ;
/


--
-- actual query
--
WITH T2 AS (
    SELECT /*+ materialize */
        UNIQUE_ID,
        GET_SEQ   SEQ_VALUE
    FROM
        (
            SELECT DISTINCT
                UNIQUE_ID
            FROM
                TT_TABLE
        ) T2IN
)
SELECT
    T1.UNIQUE_ID,
    T1.COL_ID,
    T1.VAL_ID,
    T2.SEQ_VALUE
FROM
    TT_TABLE T1,
    T2
WHERE
    T1.UNIQUE_ID = T2.UNIQUE_ID
ORDER BY
    T1.UNIQUE_ID;

Hope it is helpful.