I am running a postgresql 9.1 server and I have a table containing events information with, for each entry, an event type, a timestamp, and additional information.
I would want to write a query that would return all events of type 'a', but each returned entry should be associated to the neraest event of type 'b' (ideally, the nearest, non taking into account if it happened before or after, but if not possible, it could be the first happening just after).
By searching on the web, I found a solution base on a "LEFT JOIN LATERAL", but this is not supported by postgresql 9.1 (and I cannot update my server) :
SELECT * FROM (SELECT * FROM events WHERE type = 'a' ) as t1 LEFT JOIN LATERAL (SELECT * FROM events WHERE type = 'b' AND timestamp >= t1.timestamp ORDER BY timestamp LIMIT 1) as t2 ON TRUE;
Any idea on how to adapt this query so that it runs on 9.1 ? Or any other idea on how to perform my query ?