How to make sure HAVING happens before GROUP BY

I'm grabing a list of banks that are a certain distance from a point

ICBC                        6805    119.86727673154
Bank of Shanghai            7693    372.999006839511
Bank of Ningbo              7626    379.19406334356
ICBC                        6790    399.580754911156
Minsheng Bank               8102    485.904900718796
Standard Chartered Bank     8205    551.038506011767
Guangdong Development Bank  8048    563.713291030103
Bank of Shanghai            7688    575.327270234431
Bank of Nanjing             7622    622.249663674778

however I just want to grab 1 venue of each chain.

The query so far

SELECT name, id , (
GLength( LineStringFromWKB( LineString( `lnglat` , POINT( 121.437478728836, 31.182877821277 ) ) ) )
) *95000 AS `distance`
FROM `banks` 
WHERE (
lnglat != ""
)
AND (
published =1
)
HAVING (
distance <700
)
ORDER BY `distance` ASC

using group by name doesn't work because it evaluates then the distance does not fall into the range. In other words if there is an ICBC over 700 m away with a lower id, then ICBC will not appear in the results even though two开发者_StackOverflow社区 ICBC are withing 700 m. So I suspect this happens because group by happens before having

Or maybe there is a different solution?

I could not move the distance check to the where as it is not a real column #1054 - Unknown column 'distance' in 'where clause'

---

Select your entire query as a table and do then do the Group By on that.

E.g.

Select * FROM 
(SELECT name, id , (
GLength( LineStringFromWKB( LineString( `lnglat` , POINT( 121.437478728836, 31.182877821277 ) ) ) )
) *95000 AS `distance`
FROM `banks` 
WHERE (
lnglat != ""
)
AND (
published =1
)
HAVING (
distance <700
)
ORDER BY `distance` ASC) t
GROUP BY t.name

---

Are you sure your sample is complete as there is no GROUP BY condition? If you want banks within 700 miles then put that in WHERE condition. If you only want 1 bank reported then put that in the GROUP BY. You might need to repeat the Glenght in the Group By rather than using the alias - depends on your version of SQL. You are not grabbing a list of banks a certain distance from a point - you are grabbing banks with position and calculating the distance from a certain point. You want only banks with 700 of the calculated distance and if a bank is repeated you only want it listed once.

    SELECT name, id, (GLength( ...) AS [distance]
    FROM [banks] 
    WHERE [lnglat] != "" ... AND [distance] <700 
    Group By [name], [id], [distance] 
    ORDER BY [distance] ASC 

---

I'm not sure if this is what you are looking for, just getting one bank with the least distance.

SELECT banks.name, banks.id, banks_with_least_distance.distance
FROM banks JOIN
(
  SELECT name, min(
  GLength( LineStringFromWKB( LineString( `lnglat` , POINT( 121.437478728836, 31.182877821277 ) ) ) )
  *95000) AS `distance`
  FROM `banks` 
  WHERE (lnglat != "") AND (published =1) AND (GLength( LineStringFromWKB( LineString( `lnglat` , POINT( 121.437478728836, 31.182877821277 ) ) ) ) *95000 < 700)
  GROUP BY `name`
) AS banks_with_least_distance ON banks.name = banks_with_least_distance.name
ORDER BY banks_with_least_distance.distance DESC

Edited: changed the distance in the where clause to the actual formula.