I have the following sample table and data:
CREATE TABLE ords
( ord_nbr NUMBER NOT NULL
, step_nbr NUMBER
, area_code VARCHAR2(2)
CONSTRAINT caln_pk PRIMARY KEY (ord_nbr)
);
INSERT INTO ords
VALUES (1,1,'A1');
INSERT INTO ords
VALUES (1,2,'B1');
INSERT INTO ords
VALUES (1,3,'B1');
INSERT INTO ords
VALUES (1,4,'B2');
INSERT INTO ords
VALUES (1,5,'A1');
INSERT INTO ords
VALUES (1,6,'C1');
INSERT INTO ords
VALUES (1,7,'X1');
INSERT INTO ords
VALUES (1,8,'C2');
INSERT INTO ords
VALUES (1,9,'C2');
INSERT INTO ords
VALUES (1,10,'Z9');
The results I want to get look like:
ORD_NBR STEP_NBR AREA_CODE SEQ_NBR
-----------------------------------------------
1 1 A1 1
1 2 B1 2
1 3 B1 2
1 4 B2 3
1 5 A1 4
1 6 C1 5
1 7 X1 6
1 8 C2 7
1 9 C2 7
1 10 Z9 8
I only want the SEQ_NBR to increase if the area changes... I'm wondering if there's I need to turn this into a hierarchical query somehow, so that there's some relationship between step 1 and step 2, step 2 and step 3, etc.
I've played around with ROW_NUMBER, RANK, and DENSE_RANK, but I can't seem to figure out how to add that SEQ_NBR column...
Any suggestions?