From: | Brice André <brice(at)famille-andre(dot)be> |
---|---|
To: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | join tables by nearest timestamp |
Date: | 2017-11-01 05:53:45 |
Message-ID: | CAOBG12=_YTRw0eSY3uRKZTdEw-B-oxZ2Vi5Dwo+NdDYuc7uv6A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2017-11-01 08:06:04 | Re: join tables by nearest timestamp |
Previous Message | Andreas Joseph Krogh | 2017-10-25 21:34:05 | Re: Unable to use INSERT ... RETURNING with column from other table |