Oracle get first preceding and following rows ordered by time in another table

Question

I have 2 tables each of which have a timestamp column. How do I query for each row in A, the first preceding and following timestamps in B ?

I want:

A.id A.timestamp first_preceding(B.timestamp) first_following(B.timestamp)

Answer 1

I'd try this:

SELECT DISTINCT a.id, a.timestamp, b0.timestamp, b1.timestamp
FROM a, b b0, b b1
WHERE
b0.timestamp = (SELECT MAX(timestamp) FROM b WHERE timestamp < a.timestamp)
AND b1.timestamp = (SELECT MIN(timestamp) FROM b WHERE timestamp > a.timestamp);