Restart a counter in a table based on a another field in the same table
(1) By Gert Van Assche (GertVA) on 2020-05-02 12:15:58 [link] [source]
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
(2) By Keith Medcalf (kmedcalf) on 2020-05-02 13:22:28 in reply to 1 [link] [source]
select p,
s,
dense_rank() over (order by p),
row_number() over (partition by p)
from t
order by p, s;
(3) By Keith Medcalf (kmedcalf) on 2020-05-02 13:38:38 in reply to 2 [link] [source]
Actually you probably want to "order by 3,4".
(4) By Gert Van Assche (GertVA) on 2020-05-02 13:57:58 in reply to 3 [source]
Hi Keith, thanks a million! This is impressive.