Richard Huxton <dev@archonet.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
thecolum 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