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!

CodePudding user response：

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;

Demo