I have 3 tables with the following fields:
I want to find the closest offices to a given project
SELECT office.office_name, org.org_name,
st_distance_sphere(office.location, project.location)*0.001 as
distance
FROM office JOIN org JOIN project
WHERE office.org_id=org.org_id AND
project.project_id=7
ORDER BY distance asc;
The above query works and gives me closest offices to a project, but I need to get only 1 closest office per org
Hence I tried, the following using semi-join
SELECT office.office_name, org.org_name,
st_distance_sphere(office.location, project.location)*0.001 as
distance
FROM office JOIN org JOIN project
INNER JOIN
( SELECT org.org_id as orgid1,
min(st_distance_sphere(office.location, project.location)*0.0001) as
distance1
FROM office JOIN org JOIN project
WHERE
office.org_id=org.org_id and project.project_id=7
GROUP BY org.org_id
) AS t
ON t.orgid1=org.org_id and t.distance1=min(st_distance_sphere(office.location,project.location)*0.001)
WHERE office.org_id=org.org_id AND project.project_id=7 ;
Using this, I get the following error and the reason seems to be the min(st_distance_sphere) function. Removing that removes the error, but i don't get the desired results.
ERROR 1111 (HY000): Invalid use of group function
What am i doing wrong ? Any thoughts highly appreciated. Thanks
I need to get only 1 closest office per org
SELECT
A.office_name, A.org_name, A.distance
FROM
(SELECT
office.office_name,
org.org_name,
st_distance_sphere(office.location, project.location)*0.001 as distance
FROM office JOIN org JOIN project
WHERE office.org_id=org.org_id AND
project.project_id=7) A
JOIN
(SELECT
org.org_name,
MIN(st_distance_sphere(office.location, project.location)*0.001) as distance
FROM office JOIN org JOIN project
WHERE office.org_id=org.org_id AND
project.project_id=7
GROUP BY org.org_name) B
ON A.org_name=B.org_name AND A.distance=B.distance
ORDER BY A.distance ASC;