On 22/07/10 11:02, A. Kretschmer wrote:
> In response to Rainer Stengele :
>> 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.
-- Richard Huxton Archonet Ltd