Re: grouping subsets

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 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.

--
Richard Huxton
Archonet Ltd