>Von: pgsql-general-owner@xxxxxxxxxxxxxx [pgsql-general-owner@xxxxxxxxxxxxxx]" im Auftrag von "Elliot [yields.falsehood@xxxxxxxxx] >Gesendet: Freitag, 25. Oktober 2013 20:33 > >On 2013-10-25 13:35, Robert James wrote: >> On 10/25/13, Robert James <srobertjames@xxxxxxxxx> wrote: >>> I have a table of (timed) events, and I'm interested in marking events >>> whose properties have changed from the previous event. >>> >>> I believe this can be done with window functions, but I'm not sure >>> how. What window function can give me a field from the _previous_ >>> row? >>> >>> (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(...). >> >> 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? >> >> > >Maybe a custom aggregate that takes the last item in a set? Hello, I would misuse GUC variables for this. (using the functions current_setting and set_config) define a set get and switch fuction (I use operators for better readability) something like: select 'a' ==> 'foo' 'a' select 'b' <==> 'foo' 'a' select <== 'foo' 'b' and in your query: SELECT case when test then col <==> 'foo' else <== 'foo' end regards, Marc Mamin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
