MySQL JOIN based on closest matching timestamps

user_892134 0
Viewed 27

I have the following query:

SELECT a.* 
FROM `user_subscription_history` a
LEFT JOIN `transaction` b ON b.tran_date=a.date_time

b.tran_date and a.date_time are both timestamps. I'd like the join to return the closest matching. Currently only returns if matching.

How do i solve this?

mysql
1 Answers

You can use ROW_NUMBER() to identify the closest timestamp of another table:

select *
from (
  select a.*, 
    row_number() over(
      order by abs(timestampdiff(microsecond, b.tran_date, a.date_time))
    ) as rn
  from user_subscription_history a
  cross join `transaction` b
) x
where rn = 1
user_64361910
Related