I have a column ride_distance_km and I've tried the following:
SELECT ride_distance_km
FROM ride_data
HAVING ride_distance_km BETWEEN 1.1*min(ride_distance_km) and 0.9*max(ride_distance_km);
Query is running, but I get 0 results which is false.
Tried WHERE instead of HAVING and query didn't run.
Looking on the attached photo, I expect to filter out the 1.2km, 28.06km and 30.13km rides (for this specific data of course)
Thanks for your help
Having without Group by does not make sense when you are not using aggregate functions on the complete expression. Having is applied after the aggregation phase and may be used if you want to filter aggregate results.
Having ride_distance_km BETWEEN ... will not be able to determine which row value for ride_distance_km to consider for comparison! I believe that it is not Standard SQL, but due to (mis)feature of MySQL, it is allowed. However, there is no guarantee that it will give correct results (as explained above).
One approach would be to determine Min() and Max() values in a Derived Table. Then, we can utilize these aggregate values for further filtering inside the join On clause.
Try the following query instead:
SELECT rd.ride_distance_km
FROM ride_data AS rd
JOIN
(
SELECT MIN(ride_distance_km) AS min_ride_distance,
MAX(ride_distance_km) AS max_ride_distance
FROM ride_data
) AS dt
ON rd.ride_distance_km BETWEEN 1.1*dt.min_ride_distance AND
0.9*dt.max_ride_distance