Hallo Виктор, thanks a lot for your explanation :-) You rock! > > This example corresponds to the ORDER BY user_id, sort > while you claim you need to ORDER BY sort, user_id. > right, I confused the order. > I will explain this for the ordering that matches your sample. > > You need to group your data, but you should first create an artificial > grouping column. > > First, detect ranges of your buckets: > WITH ranges AS ( > SELECT id, user_id, key, sort, > CASE WHEN lag(key) OVER > (PARTITION BY user_id ORDER BY user_id, sort) = key > THEN NULL ELSE 1 END r > FROM foo > ) > SELECT * FROM ranges; > > Here each time a new “range” is found, «r» is 1, otherwise it is NULL. > > Now, form your grouping column: > WITH ranges AS ( > SELECT id, user_id, key, sort, > CASE WHEN lag(key) OVER > (PARTITION BY user_id ORDER BY user_id, sort) = key > THEN NULL ELSE 1 END r > FROM foo > ) > , groups AS ( > SELECT id, user_id, key, sort, r, > sum(r) OVER (ORDER BY user_id, sort) grp > FROM ranges > ) > SELECT * FROM groups; > so the trick is to flag changes in key and afterwards count them using the dynamic nature of a frame ending with the current row. great :-) Once you have a group column, it's pretty clear then. thanks Morus -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
