Nikhil Utane Nikhil Utane -4 years ago 47
SQL Question

SQL join where value in second table is first lower value w.r.t the first table

Let's say I have 2 tables and both of them have a column that contains

timestamp
for various events. The timestamp values in both the tables are different as they are for different events.
I want to join the two tables such that every record in table1 is joined with first lower timestamp on table2.

For e.g.
Table1 Table2
142.13 141.16
157.34 145.45
168.45 155.85
170.23 166.76
168.44

Joined Table should be:
142.13,141.16
157.34,155.85
168.45,166.76
170.23,168.44


I am using Apache Spark SQL.

I am a noob in SQL and this doesn't look like job for a noob :). Thanks.

Answer Source

Ditto has shown the straight-forward way to solve this. If Apache Spark really has problems with this very basic query, then join first (which can lead to a big intermediate result) and aggregate then:

select t1.v, max(t2.v)
from table1 t1
join table2 t2 on t2.v <= t1.v
group by t1.v
order by t1.v;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download