CODEWITHSUNDEEP
mysqlsql
francescoolivo
francescoolivo

Reputation: 37

MySQL Group by consecutive values and count

I have a table that looks like this

id
1
2
4
5
6
10
11

So a bunch of consecutive values, an unknown number of absent fields and then other consecutive values. What I am trying to achieve is to get

id stint
1 0
2 0
4 1
5 1
6 1
10 2
11 2

By incrementing every time the number of the stint, which I can later use for summing over other columns. Is it possible? Thanks

Upvotes: 2

Views: 854

Answers (1)

D-Shih
D-Shih

Reputation: 46219

If your MySQL version support window function.

You can try to use LAG window function in subquery to get previous id column, then use SUM condition aggregate window function.

Query #1

SELECT Id,
       SUM(id - n_Id > 1) OVER(ORDER BY id) stint
FROM (
    SELECT *,LAG(id,1,id) OVER(ORDER BY id) n_Id 
    FROM T
) t1
Id stint
1 0
2 0
4 1
5 1
6 1
10 2
11 2

View on DB Fiddle

Upvotes: 4

Related Questions