Richard Huxton <d...@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 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
