Re: [SQL] join tables by nearest timestamp

Поиск
Список
Период
Сортировка
От Achilleas Mantzios
Тема Re: [SQL] join tables by nearest timestamp
Дата
Msg-id 282b209c-eaac-2f22-ae04-6ecfdac5284b@matrix.gatewaynet.com
обсуждение исходный текст
Ответ на [SQL] join tables by nearest timestamp  (Brice André <brice@famille-andre.be>)
Ответы Re: [SQL] join tables by nearest timestamp  (Achilleas Mantzios <achill@matrix.gatewaynet.com>)
Список pgsql-sql
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
tothe 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;

>
> 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

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

Предыдущее
От: Brice André
Дата:
Сообщение: [SQL] join tables by nearest timestamp
Следующее
От: Achilleas Mantzios
Дата:
Сообщение: Re: [SQL] join tables by nearest timestamp