sqlite select minimum function value of grouped result

i have a table with gps locations of restaurants chains and would like to return the addresses of the restaurants closest to point (A) within a certain radius

SELECT *
        , MIN(distance($lat, $lon, lat, lon)) as miles 
    FROM all_restaurants 
    WHERE lat between $lat1 and $lat2 
        AND lon between $lon1 and $lon2 
        AND miles < $miles 
    GROUP BY restaurant_id 
    ORDER BY miles ASC
        , company_name ASC 
    LIMIT 500

returns error misuse of aggregate: MIN()

any ideas?

Answers


I think that your problem is that you only are grouping by restaurant_id. When you use an aggregation function as MIN, MAX, SUM, AVG, etc, you need to include every column in the SELECT statement that are not in any aggregation function. In this case you have two options, either in the SELECT you only put restaurant_id as the following:

SELECT restaurant_id 
        , MIN(distance($lat, $lon, lat, lon)) as miles 
    FROM all_restaurants 
    WHERE lat between $lat1 and $lat2 
        AND lon between $lon1 and $lon2 
        AND miles < $miles 
    GROUP BY restaurant_id 
    ORDER BY miles ASC
        , company_name ASC 
    LIMIT 500

Or you put every other column included in the '*' on the grouping (because you can't use GROUP BY *).


Need Your Help

Regular expression for removing inner double quotes

java regex csv

I have a String with outer double quotes "". There are inner double quotes that I need to remove. What is a regular expression for this?

MVC3 referencing update-able dependencies

asp.net-mvc-3 reference assemblies gac

I would like create a MVC3 website. I have existing dlls packaged as .net MSI which are installed into the GAC. What is the best way to reference these update-able dlls in MVC3? They will always be

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.