CODEWITHSUNDEEP
sqlpostgresql
KAs
KAs

Reputation: 1868

Postgresql: Join another column on the least value greater than left column

Here're two tables:

--Table A

id
1
4
7

--Table B
id
3
8
11
13

What I intend to implement is to join table A with B on id column, where right key's value is the smallest value greater than left key. For instance, id=1 in table A should join with id=3 in table B. Likewise, both id=4 and id=7 in table A should join with id=8 in table B. Is there a good way to implement this in postgresql? Thanks!

Upvotes: 1

Views: 1351

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

One method to look up the key is a correlated subquery:

select a.*,
       (select min(b.id)
        from b
        where b.id > a.id
       ) as b_id
from a;

If you need additional columns from b, you can join this back to b or use a lateral join:

select a.*, b.*
from a left join lateral
     (select b.*
      from b
      where b.id > a.id
      order by b.id
      fetch first 1 row only
     ) b on true;

Note: Your question says "greater than", so that is what the answer implements. Usually in these situations, I would expect "greater than or equal to". The adjustment for the queries is trivial.

Upvotes: 1

Related Questions