Table setup:
create table users(id bigserial, group_id bigint);
insert into users(group_id) values (1), (1), (2), (1), (3), (3);
Query:
WITH RECURSIVE r AS (
SELECT users.id, users.group_id, 0 as i
FROM users
UNION
SELECT users.id, users.group_id
, (CASE WHEN users.group_id = lag(users.group_id) over (order by users.id)
THEN (r.i)
ELSE (r.i+1) END) AS i
FROM users
INNER JOIN r ON users.id = r.id
WHERE r.i < 5)
SELECT * FROM r;
I'm trying to iterate through user, and assign i
if previous group_id
value is equal to current, i+1
otherwise, starting from 0.
However, this recursive query doesn't work as I expect, see: http://sqlfiddle.com/#!15/ad3b6/39
order by
there is no such thing as the "previous" rowid
(though that's missing from the query).