agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: Re: join tables by nearest timestamp
Date: Wed, 1 Nov 2017 10:11:36 +0200
Message-ID: <53afa96e-2c45-bb10-51f3-2a0640c5d743@matrix.gatewaynet.com> (raw)
In-Reply-To: <282b209c-eaac-2f22-ae04-6ecfdac5284b@matrix.gatewaynet.com>
References: <CAOBG12=_YTRw0eSY3uRKZTdEw-B-oxZ2Vi5Dwo+NdDYuc7uv6A@mail.gmail.com>
<282b209c-eaac-2f22-ae04-6ecfdac5284b@matrix.gatewaynet.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 01/11/2017 10:06, Achilleas Mantzios wrote:
> On 01/11/2017 07:53, Brice André wrote:
>> 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 ?
> smth like :
>
> SELECT l1.*,l2.logtime,l2.category,l2.username from logging l1 LEFT OUTER JOIN logging l2 ON ('t') where l1.category='vsl.login' AND (l2.category IS NULL OR l2.category='vsl.SpareCases') AND
> (l2.logtime IS NULL OR l2.logtime>=l1.logtime) order by l1.logtime;
oopss, sorry I forgot, you'll have to add a DISTINCT ON and order by l2.logtime in order to have what you want :
SELECT DISTINCT ON (l1.logtime,l1.category,l1.username,l1.action) l1.*,l2.logtime,l2.category,l2.username from logging l1 LEFT OUTER JOIN logging l2 ON ('t') where l1.category='vsl.login' AND
(l2.category IS NULL OR l2.category='vsl.SpareCases') AND (l2.logtime IS NULL OR l2.logtime>=l1.logtime) order by l1.logtime,l1.category,l1.username,l1.action,l2.logtime;
>
>>
>> Thanks in advance,
>> Brice
>
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (8+ messages) latest in thread
Message-ID: <53afa96e-2c45-bb10-51f3-2a0640c5d743@matrix.gatewaynet.com>
Permalink: ../53afa96e-2c45-bb10-51f3-2a0640c5d743@matrix.gatewaynet.com/
Also on: postgresql.org/message-id/53afa96e-2c45-bb10-51f3-2a0640c5d743@matrix.gatewaynet.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: join tables by nearest timestamp
In-Reply-To: <53afa96e-2c45-bb10-51f3-2a0640c5d743@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox