clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601472 fiddles created (47959 in the last week).

create table users(id bigserial, group_id bigint);
 hidden batch(es)


insert into users(group_id) values (1), (1), (2), (1), (3), (3);
6 rows affected
 hidden batch(es)


SELECT id, group_id, count(step) OVER (ORDER BY id) AS i FROM ( SELECT id, group_id , lag(group_id, 1, group_id) OVER (ORDER BY id) <> group_id OR NULL AS step FROM users ) sub;
id group_id i
1 1 0
2 1 0
3 2 1
4 1 2
5 3 3
6 3 3
 hidden batch(es)


CREATE OR REPLACE FUNCTION f_users_i() RETURNS TABLE (id bigint, group_id bigint, i bigint) AS $func$ DECLARE _last_group_id bigint; -- aux var to remember last group BEGIN i := 0; -- init to 0 FOR id, group_id IN SELECT u.id, u.group_id FROM users u ORDER BY u.id LOOP IF group_id <> _last_group_id THEN -- next group starts i := i + 1; END IF; _last_group_id := group_id; -- remember last group RETURN NEXT; END LOOP; END $func$ LANGUAGE plpgsql;
 hidden batch(es)


SELECT * FROM f_users_i();
id group_id i
1 1 0
2 1 0
3 2 1
4 1 2
5 3 3
6 3 3
 hidden batch(es)