Robert Stanford <rstanford@gmail.com> writes: > Can anyone suggest an elegant way to coalesce consecutive rows so only the > first start time and last end time for each group of events (by input) is > returned.
Window functions don't change the number of query result rows, so that idea is a dead end. The standard way to solve this is GROUP BY:
SELECT Input, MIN(Start) AS Start, MAX(End) AS End FROM ... GROUP BY Input ORDER BY Input -- optional
Assuming that input group 4 really is supposed to show up twice in the output then the final solution is going to have to be a combination of this and window functions. You need the later in order to be able to say "input 4, appearance 1" and "input 4, appearance 2" - which lead/lag tends to help with. Then the group by becomes {input, appearance}.
