Set a counter sql

Question

I am not an expert in sql and I am trying to find a solution on the below problem.

I have the following data

test   -    test1 
31477831 -  a1 
31477831 -  a0 
31477831 -  a1 
31477831 -  a0 
31477831 -  a0 
31477832 -  a0 
31477832 -  a0 
31477832 -  a1 

And I need to set a counter as below:

test -     test1    - counter
31477831 -  a1      - 0
31477831 -  a0      - 1
31477831 -  a1      - 2
31477831 -  a0      - 3
31477831 -  a0      - 3
31477832 -  a0      - 0
31477832 -  a0      - 0
31477832 -  a1      - 1

When the test filed change need to start from 0 when test1 field change need to increment by 1. Thanks in advance.

Answer 1

You could use analytic functions (SQL Server/Oracle/MySQL 8.0+/PostgreSQL/...):

WITH cte AS (
  SELECT *, 
    CASE WHEN LAG(test1) OVER(PARTITION BY test ORDER BY id) <> test1 
         THEN 1 ELSE 0 END l
  FROM tab
)
SELECT *, SUM(l) OVER(PARTITION BY test ORDER BY id) AS counter
FROM cte
ORDER BY id;

DBFiddle Demo

For stable sort you need some kind of id/timestamp column.