Say I have a table that stores state transitions over time like so:
id, transitionable_id, state1, state2, timestamp
I'm trying to write a query that coalesces changes in state2 away to produce just a list of transitions of state1. I guess it would look something like
SELECT state1, FIRST(timestamp)
FROM table
but I have no idea how to aggregate just the repeated state1 rows.