SQLite Forum

Restart a counter in a table based on a another field in the same table
Login

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 [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 [link] [source]

Hi Keith, thanks a million! This is impressive.