Re: grouping subsets

Richard Huxton <dev(at)archonet(dot)com> wrote:

>>> What I want to get is the values grouped by "subset", where a subset is a set of rows with identical column until the colum changes.
>>> Is there a way to get

>>> | 2 | B |
>>> | 4 | C |
>>> | 4 | B |
>>> | 3 | D |

>>> by SQL only?

>> I think, the problem is that there are 2 identical groups. I think, you
>> can write a pl/pgsql-proc, selecting all ordered by the date-field and
>> walking through the result to do the grouping, checking if the 2nd
>> column is different from the previous.

>> With plain SQL it's maybe possible too, but i don't know how ...

> It should be do-able in 8.4 onwards, look into windowing
> functions. In particular the lag() function:

> SELECT
> mycode,
> mydate,
> lag(mycode) OVER (ORDER BY mydate) AS prev_code
> FROM
> mytable
> ORDER BY mydate;

> It should be possible to use that as a subquery with an
> outer query that compares mycode=prev_code to get a run
> length.

Hmmm. Can the outer query be done without using "WITH
RECURSIVE"?

Tim