Robert James wrote > * Finding field x of the _previous_ row that meets a certain criteria > (which the current row may or may not meet) > ) This question could be phrased better. I provide an answer to my interpretation below. You'll need to play with the frame definition because I don't yet have that syntax memorized and am too lazy to figure it out right now. The following gives, for every row, the last "val" having a value less than 25. It does this by converting all other values to NULL than returning the most proximate value that is not null. The ORDER BY in the OVER() clause gives you an "unbounded preceding to current row" frame by default so the current row is a valid value for the final answer. WITH data (key, val) AS ( VALUES (1,10),(2,20),(3,30),(4,10),(5,25) ) SELECT key, val, array_last_nonnull(array_agg(CASE WHEN val < 25 THEN val ELSE NULL END) OVER (ORDER BY key)) FROM data where "array_last_nonnull(...)" is defined as: CREATE OR REPLACE FUNCTION array_last_nonnull(in_array anyarray) RETURNS anyelement AS $$ SELECT unnest FROM ( SELECT unnest, row_number() OVER () AS array_index FROM ( SELECT unnest($1) ) explode ) filter WHERE unnest IS NOT NULL ORDER BY array_index DESC LIMIT 1; $$ LANGUAGE sql STRICT IMMUTABLE ; This is probably not the most preformant solution but it is fairly simple, easy to debug (i.e., you can always view the array_agg data), and gives you a benchmark to compare against should you attempt alternatives. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Detecting-change-in-event-properties-tp5775959p5775971.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
