title: SQL: Aggregate one column based on another
body: <p>I'm stumped with this query.  I have a deal table with deals for different doctors.  There is a many to many between deals and doctors (more than one doctor per deal is possible).  I need to find the deals with the closest doctors to a given user's latitude and longitude.  I have a stored procedure that takes care of finding the distance.</p>
  
  <pre><code>    Select d.dealID, do.doctorID,
      dbo.fn_latlongdist($userlat,$userlong,do.doctorLatitude,do.doctorLongitude) as distance
      From y_Deals d
      JOIN y_deals_doctor dd ON dd.dealID = d.dealID
      JOIN Doctor do on dd.doctorID = do.doctorID 
      ORDER BY distance
  </code></pre>
  
  <p>Now I want to group by the dealID so I don't return multiple deals.  Problem is I want to return the doctorID with the minimum distance. Doesn't seem like there's any aggregate function that would surround doctorID that would return the doctorID with the minimum distance column.  </p>
  
  <p>How should I approach this?</p>