title: Distance-based JOIN given Latitude/Longitude
body: <p>Given the following tables:</p>
  
  <pre><code>table A (id, latitude, longitude)
  table B (id, latitude, longitude)
  </code></pre>
  
  <p>how do I build an efficient T-SQL query that associates each row in A with the closest row in B?</p>
  
  <p>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:</p>
  
  <pre><code>(A.id, B.id, distanceAB)
  </code></pre>
  
  <p>I have a function that calculates the distance given 2 pairs of latitude and longitude. I tried something using <code>order by ... limit 1</code> and/or <code>rank() over (partition by ...) as rowCount ... where rowCount = 1</code> but the result is either not really what I need or it takes too long to return. </p>
  
  <p>Am I missing something? </p>