CODEWITHSUNDEEP
sqlsql-server
DannyLeavitt
DannyLeavitt

Reputation: 279

SQL: Aggregate one column based on another

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.

    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

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.

How should I approach this?

Upvotes: 1

Views: 3499

Answers (2)

amit_g
amit_g

Reputation: 31250

See if this works for you

Select * From
(
    Select
          d.dealID
        , do.doctorID
        , dbo.fn_latlongdist($userlat, $userlong, do.doctorLatitude, do.doctorLongitude) as Distance
        , ROW_NUMBER() OVER (PARTITION BY d.dealID ORDER BY dbo.fn_latlongdist($userlat, $userlong, do.doctorLatitude, do.doctorLongitude)) AS RowNumber
    From
        y_Deals d
    JOIN
        y_deals_doctor dd ON dd.dealID = d.dealID
    JOIN
        Doctor do on dd.doctorID = do.doctorID 
) T
Where
    T.RowNumber = 1

UPDATE:

;With AllData As
(
    Select
          d.dealID
        , do.doctorID
        , dbo.fn_latlongdist($userlat, $userlong, do.doctorLatitude, do.doctorLongitude) as Distance
        , ROW_NUMBER() OVER (PARTITION BY d.dealID ORDER BY dbo.fn_latlongdist($userlat, $userlong, do.doctorLatitude, do.doctorLongitude)) AS RowNumber
    From
        y_Deals d
    JOIN
        y_deals_doctor dd ON dd.dealID = d.dealID
    JOIN
        Doctor do on dd.doctorID = do.doctorID 
)
,DrCount As
(
    Select
          dealID
        , Count(Distinct doctorID) as doctorCount
    From
        AllData
    Group By
        dealID
)
Select
    *
From
    AllData A
Inner Join
    DrCount C
On
    A.dealID = C.dealID
Where
    A.RowNumber = 1

Upvotes: 1

Darryl
Darryl

Reputation: 6217

Another option is to use the technique discussed in http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/. It doesn't depend on ROW_NUMBER(), which isn't available for a lot of DB engines.

Here's an example where distance is a column in the doctor table instead of a calculated function. Adapt as needed:

SELECT
  Doctors.ID, DoctorName, Distance, DealName
FROM
  (SELECT DealID, Min(Doc.Distance) as Dist
   FROM  DoctorDeals, Doctors as Doc
   WHERE  DoctorDeals.DoctorID = Doc.ID
   GROUP BY  DealID) as T, 
  DoctorDeals, Deals, Doctors
WHERE
  T.Dist = Doctors.Distance
AND
  T.DealID = DoctorDeals.DealID
AND
  DoctorDeals.DoctorID = Doctors.ID
AND 
  DoctorDeals.DealID = Deals.ID

Upvotes: 1

Related Questions