update sequential number within grouped keys
Hi,
I got the following table
DROP TABLE test;
CREATE TABLE test (k1 NUMBER, k2 NUMBER, k3 VARCHAR2(3), sort_num NUMBER);
INSERT INTO test VALUES (1,1,'a',null);
INSERT INTO test VALUES (1,1,'b',null);
INSERT INTO test VALUES (1,2,'a',null);
INSERT INTO test VALUES (2,1,'c',null);
INSERT INTO test VALUES (2,2,'e',null);
COMMIT;
I want to update the sort_num to be sequentially order by k3 group by k1 and k2. So I did the following
UPDATE test SET sort_num = Row_Number() over (PARTITION BY k1,k2 ORDER BY k3)
ORA-30483: window functions are not allowed here
UPDATE (
SELECT k1,k2,k3, sort_num,
I got the following table
DROP TABLE test;
CREATE TABLE test (k1 NUMBER, k2 NUMBER, k3 VARCHAR2(3), sort_num NUMBER);
INSERT INTO test VALUES (1,1,'a',null);
INSERT INTO test VALUES (1,1,'b',null);
INSERT INTO test VALUES (1,2,'a',null);
INSERT INTO test VALUES (2,1,'c',null);
INSERT INTO test VALUES (2,2,'e',null);
COMMIT;
I want to update the sort_num to be sequentially order by k3 group by k1 and k2. So I did the following
UPDATE test SET sort_num = Row_Number() over (PARTITION BY k1,k2 ORDER BY k3)
ORA-30483: window functions are not allowed here
UPDATE (
SELECT k1,k2,k3, sort_num,
0