SQLite Forum

That should be fast as long as the second table has an index on the timestamp column.

Personally, I think it's clearer without multiple CTEs:

<pre>
    select id as id1,
        (select id2 from
            (select id2, delta from
                (select t2.id as id2, t2.ts-t1.ts as delta from t2
                 where t2.ts>=t1.ts
                 order by t2.ts
                 limit 1)
            union all select id2, delta from
                (select t2.id as id2, t1.ts-t2.ts as delta from t2
                 where t2.ts<=t1.ts
                 order by t2.ts desc
                 limit 1)
            order by delta
            limit 1)) as id2
    from t1;
</pre>