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