Given the following tables:
table A (id, latitude, longitude)
table B (id, latitude, longitude)
how do I build an efficient T-SQL query that associates each row in A with the closest row in B?
The ResultSet should contains all the rows in A and associate them with 1 and only 1 element in B. The format that I'm looking for is the following:
(A.id, B.id, distanceAB)
I have a function that calculates the distance given 2 pairs of latitude and longitude. I tried something using order by ... limit 1
and/or rank() over (partition by ...) as rowCount ... where rowCount = 1
but the result is either not really what I need or it takes too long to return.
Am I missing something?
It's possible with the join of two subqueries. The first contains all distances between A and B locations, the second contains only the minimum distance of B locations from A locations.