join tables by nearest timestamp

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

Responses

Browse pgsql-sql by date

  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