I have two tables table1
and table2
I need to join these tables with keys that are not exactly matched, they could differ in the last 3 or 4 digits there is no fixed pattern, the joining will have two conditions:
- 1st Condition:
table2.Key_2
must be numerically greater thantable1.Key_1
so join will will search for nearest large Key - 2nd Condition:
table1
could have two keys approximately matched with one key intable2
so the join will consider the nearest large one the other return Null
|Key_1 |
|------------------|
|111330317223636588|
|121199074223629055|
|47256714523667238 |
|101348062023590858|
|106331320423644206|
|106061712623646625|
|120562195823631381|
|155570817823642550|
|114948476223640334|
|103285939423669298|
|103285939423669910|
|Key_2 |Value|
|-------------------+-----|
|111330317223636610 |Done |
|121199074223629090 |Done |
|47256714523667300 |Done |
|101348062023590900 |Done |
|106331320423644305 |Done |
|106061712623647100 |Done |
|120562195823631399 |Done |
|155570817823642677 |Done |
|114948476223640455 |Done |
|103285939423669988 |Done |
the expected result will be like the below:
|Key_1 |Value|
|-------------------+-----|
|111330317223636588 |Done |
|121199074223629055 |Done |
|47256714523667238 |Done |
|101348062023590858 |Done |
|106331320423644206 |Done |
|106061712623646625 |Done |
|120562195823631381 |Done |
|155570817823642550 |Done |
|114948476223640334 |Done |
|103285939423669298 |NULL |
|103285939423669910 |Done |
This join on the next value is hard to express as a join, but simple using this approach:
The cte combines both tables:
LEAD finds the next row's value:
and the final QUALIFY removes all rows from the 2nd table