On 10/25/13, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert James <srobertjames@gmail.com> writes: >>> (To elaborate, I'm interested in: >>> * Finding field x of the _previous_ row >>> * Finding field x of the _next_ row >>> * Finding field x of the _previous_ row that meets a certain criteria >>> (which the current row may or may not meet) >>> ) > >> The first two are actually trivial - lag(field_x) over (order by [same >> order as query]) and lead(...). > > Right. > >> But the last one seems ellusive - How can I find the value of field x >> on the previous row WHERE a criteria is met? Is it possible to do this >> at all with a window function? > > I don't see any way to achieve that with any of the built-in window > functions, but I believe it could be done by a custom window function. > Are you up for some C coding?
Hmmm... certainly nothing I would trust on a production db.
Is there a way to do it without C not using window functions? Perhaps with some type of JOIN?
you can write a table function with inner loop cycle over cursor