PostgreSQL -Assign Unique ID to each groups of records, based on a condition

I am trying to group records of a table, based on a condition, and then asing in a new column a simple integer ID for each group obtained from where the condition is met.

I need to assign the same ID to those rows whose TELEGRAMMZAEHLER number is consecutive to the next one (for example, rows 2 and 3 have TZ 5002 and 5001, therefore they are consecutive and should belong to a same Group ID.)

The GRUPPE column would be my desired outcome. Rows 2 to 4 belong together in the same group ID, bur then rows 5 and 6 should have another ID, because the TZ in row 5 is not consecutive from the TZ in row 4.

Any ideas on how can that be achieved on postgreSQL?

Thank you very much!

>Solution :

We can use LAG here along with SUM as an analytic function:

WITH cte AS (
    SELECT *, CASE WHEN TELEGRAMMZAEHLER =
                        LAG(TELEGRAMMZAEHLER) OVER (ORDER BY TMSTP DESC) - 1
                   THEN 0 ELSE 1 END AS idx
    FROM yourTable
)

SELECT ID, TMSTP, CAT_TYPE, TELEGRAMMZAEHLER,
       SUM(idx) OVER (ORDER BY TMSTP DESC) AS GRUPPE
FROM cte
ORDER BY TMSTP DESC;