[SQL] join tables by nearest timestamp

Поиск
Список
Период
Сортировка
От Brice André
Тема [SQL] join tables by nearest timestamp
Дата
Msg-id CAOBG12=_YTRw0eSY3uRKZTdEw-B-oxZ2Vi5Dwo+NdDYuc7uv6A@mail.gmail.com
обсуждение исходный текст
Ответы Re: [SQL] join tables by nearest timestamp
Список pgsql-sql
Дерево обсуждения
Dear all,

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 ?

Thanks in advance,
Brice

В списке pgsql-sql по дате отправления: