Limiting A Query To The Top 30 Unique Results

[3dColor]

Zipcode 350 mile radius search.

<!--- Set the raduis of the search in miles --->
<cfset radiusMiles = 350>

<!--- Get one zipcode to create a raduis around --->
<cfquery name="GetZipInfo" datasource="#Application.DSN#">
	SELECT * 
	FROM zipcodes
	WHERE zip = 80202
</cfquery>

<!--- Get all zipcodes with the raduis --->
<cfquery datasource="#Application.DSN#" name="GetZipsWithinRaduis">
	SELECT	zip, latitude, longitude, stateAbv, city,
			ROUND((ACOS((SIN(#GetZipInfo.latitude#/57.2958) * SIN(latitude/57.2958)) +
			(COS(#GetZipInfo.latitude#/57.2958) * COS(latitude/57.2958) *
			COS(longitude/57.2958 - #GetZipInfo.longitude#/57.2958))))
			* 3963) AS distance
	FROM	zipcodes
	WHERE	(latitude >= #GetZipInfo.latitude# - (#radiusMiles#/111))
			AND (latitude <= #GetZipInfo.latitude# + (#radiusMiles#/111))
			AND (longitude >= #GetZipInfo.longitude# - (#radiusMiles#/111))
			AND (longitude <= #GetZipInfo.longitude# + (#radiusMiles#/111))
	ORDER BY distance
	LIMIT 30
</cfquery>

<cfdump var="#GetZipsWithinRaduis#" label = "GetZipsWithinRaduis">

Here is the output of the dump:

My question is: I want to LIMIT the query to the top 30 UNIQUE cities (not all Denver) - how can I do that?

Thanks,
Dave

 

[r937]

to limit the results to the top 30 distinct cities, you can use GROUP BY, but you have to make sure that the only non-aggregate column you have in the SELECT clause is the city -- all the other expressions in the SELECT clause will then have to be aggregates

this means that if you are still interested in distance, only aggregate distance expressions are allowed

we can use your existing query as the source of the data, by writing it as a derived table (i.e. subquery in the FROM clause) in an outer query:

SELECT city
     , MIN(distance) AS min_distance
     , AVG(distance) AS avg_distance
     , MAX(distance) AS max_distance
  FROM (
       SELECT zip
            , latitude
            , longitude
            , stateAbv
            , city
            , ROUND((ACOS((SIN(#GetZipInfo.latitude#/57.2958) *                        
                           SIN(latitude/57.2958)) +
                          (COS(#GetZipInfo.latitude#/57.2958) * 
                           COS(latitude/57.2958) *
                           COS(longitude/57.2958 - 
                               #GetZipInfo.longitude#/57.2958))))
                       * 3963) AS distance
         FROM zipcodes
        WHERE latitude >= #GetZipInfo.latitude# - #radiusMiles#/111
          AND latitude <= #GetZipInfo.latitude# + #radiusMiles#/111
          AND longitude >= #GetZipInfo.longitude# - #radiusMiles#/111
          AND longitude <= #GetZipInfo.longitude# + #radiusMiles#/111
       ) AS data
GROUP
    BY city
ORDER 
    BY avg_distance ASC LIMIT 30

note here we are getting the top 30 nearest cities based on average distance

another benefit of using a subquery is that the column alias for distance, that humongous expression, can be used multiple times in the outer query (3 times in the SELECT clause, once in the ORDER BY clause) without the need to use the humongous expression instead





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[3dColor]

Rudy,

Thanks for answering my question, it is amazing what you can do with a SQL statement!

I had no idea you were still hanging out in the Crystaltech forums. So instead of continuing the conversation here, please see my reply over there.

http://forums.crystaltech.com/index.php/topic,34018.0.html

Thanks!
Dave