Create SQLite View From Two Tables Recursively

Question

I have a column on Table A with numbers and a column on Table B different set of numbers. How do i create a view of table A numbers with a second column of the closest numbers from B.

---Table ColA---|---Table ColB---

13 | 22

25 | 49

22 | 11

33 | 36

I want for the VIEW results to be

---View ColA---|---View ColB---

13 | 11

25 | 22

22 | 22

33 | 36

Answer 1

Depending on how much data you are looking at and if this is only a one off query vs calling it several times, a quick and dirty solution could be to simply cross join the tables, ...

... and using the functions ROW_NUMBER (link to the documentation) to select the correct entry the ABS(link to the documentation) function to calculate the absolute difference between the to columns and simply wrap it all in a subquery, to keep things clean.

Could look something like this:

SELECT t1num, t2num FROM (
    SELECT 
        ROW_NUMBER() OVER(Partition BY t1.num ORDER BY t1.num, ABS(t1.num - t2.num) ) rowNum,
        t1.num t1num, 
        t2.num t2num 
    FROM Table1 t1 CROSS JOIN Table2 t2) SubQuery
 WHERE rowNum = 1