Hi All, I need to create 2 counters from 1 table.
One counter (C1) has to increase per value (P), the other counter (C2) has to restart at 1 when C1 increases.
Not sure I explain well what I need. Maybe 2 tables will help:
This is my table.
CREATE TABLE T (P, S);
INSERT INTO T(P, S) VALUES('ABCDE', 'A');
INSERT INTO T(P, S) VALUES('ABCDE', 'B');
INSERT INTO T(P, S) VALUES('ABCDE', 'C');
INSERT INTO T(P, S) VALUES('ABCDE', 'D');
INSERT INTO T(P, S) VALUES('ABCDE', 'E');
INSERT INTO T(P, S) VALUES('XYZ', 'X');
INSERT INTO T(P, S) VALUES('XYZ', 'Y');
INSERT INTO T(P, S) VALUES('XYZ', 'Z');
This is the output 1 would need:
P S C1 C2
ABCDE A 1 1
ABCDE B 1 2
ABCDE C 1 3
ABCDE D 1 4
ABCDE E 1 5
XYZ X 2 1
XYZ Y 2 2
XYZ Z 2 3
I can create the counter C1 by taking the rowid from a select distinct P.
But I have no idea how to create C2.
I'm sure there is a mechanism for this, but I don't even know how this grouping is called, which makes it hard to find what I need. So your help is very appreciated.
thanks
Gert