Vitali Ponomar Vitali Ponomar - 5 months ago 25
SQL Question

Postgres: how to join closest value from the same table

I have a the following table

CREATE TABLE temp (
id SERIAL,
other_id INTEGER NOT NULL, -- some ForeignKey
date DATE NOT NULL
)


I want to join this table to itself by previous (closest)
date
item with the same
other_id
. Something like

SELECT count(*)
FROM temp AS t1
JOIN temp AS t2 ON (t2.other_id = t1.other_id AND t2.date < t1.date)


But
t2.date
must be closest to
t1.date
(not any lower date).

Is that possible at all?

Answer

You can use a query like the following:

WITH temp_rn AS (
  SELECT id, other_id, date,
         ROW_NUMBER() OVER (PARTITION BY other_id 
                            ORDER BY date) AS rn
  FROM temp
)
SELECT t1.*
FROM temp_rn AS t1
LEFT JOIN temp_rn AS t2 ON t1.other_id = t2.other_id AND t1.rn = t2.rn + 1

The query uses ROW_NUMBER in order to detect the 'previous' row: it is the one having the previous row number within the same other_id slice.