SQL Language (MOSC)

MOSC Banner

update sequential number within grouped keys

edited Nov 8, 2010 11:59PM in SQL Language (MOSC) 6 commentsAnswered
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,

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center