This question already has an answer here:
I have a table (cargo_route) that consists of following fields:
id,
truck_id,
lat,
lon,
radius
it's a shipping route where each row is each stop with lat,lon coordinates for a truck.
I need to calculate a distance between each stop and my (any input) point (lat lon) and bring results if the distance is less than specified radius (for that stop). (Basically search whether the city is on the route or too far away from any stop on the route)
HERE is my query (which works fine)
SELECT
`truck_id`,
`radius`,
(
3959 * acos(
cos(radians(39.9611755)) * cos(radians(cargo_route.lat)) * cos(
radians(cargo_route.lon) - radians(- 82.9987942)
) + sin(radians(39.9611755)) * sin(radians(cargo_route.lat))
)
) AS distance FROM
(`cargo_route`) HAVING `distance` < `radius`
The code works.... BUT, it brings me several rows with the same truck_id, (basically every single stop that's close enough to the searched point)
When I try to "GROUP BY truck_id" to only get ids of trucks that pass through my point, I get an empty set :(
I could resolve this within PHP logic but I'd rather get SQL to return me what I really need than iterate through an array.
I did research and learned that GROUP BY executes first and HAVING ends up "not having" distance < radius because group by grabs the first row (hence HAVING producing the empty set). I also tried ORDER B but it has no effect.
HEre is the query that returns EMPTY SET
SELECT
`truck_id`,
`radius`,
(
3959 * acos(
cos(radians(39.9611755)) * cos(radians(cargo_route.lat)) * cos(
radians(cargo_route.lon) - radians(- 82.9987942)
) + sin(radians(39.9611755)) * sin(radians(cargo_route.lat))
)
) AS distance FROM
(`cargo_route`) GROUP BY truck_id HAVING `distance` < `radius`
QUESTION: IS there way to execute HAVING and then group by after? Perhaps a slightly different syntax?
I tried reordering HAVING and GROUP BY but it throws me an error. ALso, I cannot use WHERE because 'distance' is an aggregated column.
************EDIT _ SOLVED *******************
Canno answer my own question:
Guys, actually found an answer here: SELECT From MySQL View With HAVING Clause Returns Empty Result Set
Basically, put SELECT * FROM (my query) S WHERE distance < radius GROUP BY truck_id
</div>
It might work if you move distance evaluation directly to WHERE clause. You can also wrap everything(without GROUP BY) into subquery and in GROUP BY in parent query.
Remember that having is meant to work with aggregate data (that is, if you want to use having then you need to define a grouping criteria).
A quick guide I keep is this:
select [the data you want]
from [the tables you have]
where [the filters you need to apply ON YOUR "RAW" DATA]
group by [your grouping criteria]
having [the filters you need to apply TO THE GROUPED DATA]
QUESTION: IS there way to execute HAVING and then group by after?
HAVING is for filtering output data. WHERE operates on input data.
You have to use some subquery, maybe like this.
SELECT `truck_id`, `radius`, `distance`
FROM
(
SELECT
`truck_id`,
`radius`,
(
3959 * acos(
cos(radians(39.9611755)) * cos(radians(cargo_route.lat)) * cos(
radians(cargo_route.lon) - radians(- 82.9987942)
) + sin(radians(39.9611755)) * sin(radians(cargo_route.lat))
)
) AS distance FROM
(`cargo_route`) GROUP BY truck_id;
)
HAVING `distance` < `radius`;
MySQL is leading you astray. The syntax that works for you is actually illegal.
Based on your stated intent, the haing clause should be a where.
SELECT
`truck_id`,
`radius`,
(
3959 * acos(
cos(radians(39.9611755)) * cos(radians(cargo_route.lat)) * cos(
radians(cargo_route.lon) - radians(- 82.9987942)
) + sin(radians(39.9611755)) * sin(radians(cargo_route.lat))
)
) AS distance FROM
(`cargo_route`) where (
3959 * acos(
cos(radians(39.9611755)) * cos(radians(cargo_route.lat)) * cos(
radians(cargo_route.lon) - radians(- 82.9987942)
) + sin(radians(39.9611755)) * sin(radians(cargo_route.lat))
)
)` < `radius`