title: Pull a separate column that matches the (min) of an aggregate function
body: <p>It works well so far but I am stumped from here as I am brand new to this. This query finds the closest distance match, pairing up every item in the &quot;FAILED&quot; folder against everything that isn't in the &quot;FAILED&quot; folder.</p>
  <p>There is a column &quot;RouteID&quot; in the &quot;table p&quot; that I want to match up with the min() aggregate.</p>
  <p>I cannot process how to make the SELECT query simply show the associated &quot;RouteID&quot; column from tbl p but ultimately, I want to turn this into an update query that will SET a.Route = p.Route that is associated with the min()</p>
  <p>Any help would be appreciated.</p>
  <pre><code>SELECT a.name, a.Reference1,
      MIN(round(ACOS(COS(RADIANS(90-a.lat))
       *COS(RADIANS(90-p.latpoint))
       +SIN(RADIANS(90-a.lat))
       *SIN(RADIANS(90-p.latpoint))
       *COS(RADIANS(a.lon-p.longpoint)))
       *3958.756,2)) AS 'DISTANCE'
  
  FROM tblOrder AS a WITH (NOLOCK)
  LEFT JOIN 
       (
           SELECT b.lat AS latpoint, b.lon AS longpoint,
               b.Sequence, b.routeid
               from tblOrder b WITH (NOLOCK)
                WHERE b.CUSTID = 180016
                AND b.routeID &lt;&gt; 'FAILED'
                AND b.StopType = 1
       ) AS p ON 1=1
  
  WHERE a.CustID = 180016
  AND a.RouteID = 'FAILED'
  AND a.StopType = 1
  AND P.RouteID &lt;&gt; 'FAILED'
  
  GROUP BY
  a.name, a.Reference1
  </code></pre>