reset index in dense_rank or row_number after variable partitioning over changes

Question

I'm using DB2 SQL. I have the following:

select * from mytable order by Var,Varseq

ID    Var    Varseq
--    ---    ------
1     A      1
1     A      2
1     B      1
1     A      3
2     A      1
2     C      1

but would like to get:

ID    Var    Varseq    NewSeq
--    ---    ------    ------
1     A      1         1
1     A      2         2
1     B      1         1
1     A      3         1
2     A      1         1
2     C      1         1

However dense_rank produces the same as the original result. I hope you can see the difference in the desired output - in the 4th line when ID=1 returns to Var=A, I want the index reset to 1, instead of carrying on as 3. i.e. I would like the index to be reset every time Var changes for a given ID.

for ref here was my query:

SELECT *, DENSE_RANK() OVER (PARTITION BY ID, VAR ORDER BY VARSEQ) FROM MYTABLE

Answer 1

This is an example of a gaps-and-islands problem. However, SQL tables represent unordered sets. Without a column that specifies the overall ordering, your question does not make sense.

In this case, the difference of row numbers will do what you want. But you need an overall ordering column:

select t.*,
       row_number() over (partition by id, var, seqnum - seqnum2 order by <ordering col>) as newseq
from (select t.*,
             row_number() over (partition by id order by <ordering col>) as seqnum,
             row_number() over (partition by id, var order by <ordering col>) as seqnum2
      from t
     ) t