Trouble with SQLite SQL Query

Question

I'm trying to run the following query in SQLite 3:

SELECT *,
  DISTANCE(latitude, longitude, ?, ?) AS "distance"
FROM "country"
WHERE "id" NOT LIKE ?
HAVING "distance" <= ?
ORDER BY "distance" ASC;

But I get the following error:

SQLSTATE[HY000]: General error: 1 a GROUP BY clause is required before HAVING

I don't understand why SQLite wants me to group results, but still I tried the following:

SELECT *,
  DISTANCE(latitude, longitude, ?, ?) AS "distance"
FROM "country"
WHERE "id" NOT LIKE ?
GROUP BY "id"
HAVING "distance" <= ?
ORDER BY "distance" ASC;

And I also tried this:

SELECT *,
  DISTANCE(latitude, longitude, ?, ?) AS "distance"
FROM "country"
WHERE "id" NOT LIKE ?
GROUP BY "distance"
HAVING "distance" <= ?
ORDER BY "distance" ASC;

No errors, but all records were returned (even those having "distance" > ?). I also tried doing:

SELECT *,
  DISTANCE(latitude, longitude, ?, ?) AS "distance"
FROM "country"
WHERE "id" NOT LIKE ?
  AND "distance" <= ?
ORDER BY "distance" ASC;

Same output, all records were returned. I've double checked - the distance is being correctly calculated... I've no idea what's wrong with this query, can someone help me out?

Answer 1

Dear Bro,

it is syntax error, you must have to use 'group by' when you are using having cause,

your query with group by is fetching records having ("distance" >) because, there is database rule that first of all it takes data with matching records then it will perform group by on it after it it is filtering records by having cause. so you never get data having ("distance" <)

please correct if i am wrong

Answer 2

You can't specify a HAVING clause without having specified a GROUP BY clause. Use:

  SELECT *, 
         DISTANCE(latitude, longitude, ?, ?) AS dist
    FROM COUNTRY c
   WHERE c.id NOT LIKE ?
     AND DISTANCE(c.latitude, c.longitude, ?, ?) <= ?
ORDER BY dist;

If you don't want to call DISTANCE more than once, you can use a subquery:

  SELECT x.*
    FROM (SELECT c.*, 
                 DISTANCE(latitude, longitude, ?, ?) AS dist
            FROM COUNTRY c
           WHERE c.id NOT LIKE ?) x
   WHERE x.dist <= ? 
ORDER BY dist;

Answer 3

A better (and quicker) approach might be to reduce down the SELECTed set before applying the ORDER BY. I use this kind of approach:

SELECT * FROM Locations WHERE abs(Latitude - 51.123) < 0.12 AND abs(Longitude - 0.123) < 0.34 ORDER BY DISTANCE(Latitude, Longitude, 51.123, 0.123)

...where (51.123, 0.123) is the centre latitude / longitude point you're searching relative to, and the values of 0.12 and 0.34 are used to narrow down your search to a lat/long square-on-a-sphere of an appropriate size (i.e. a square of n kilometres by n kilometres at that point on the Earth's sphere, where the size depends on the average geographical distribution of your locations). I use the degree length formulae from http://en.wikipedia.org/wiki/Longitude to work out what these values should be given the search point's position on the Earth's sphere.

Answer 4

Further to the correct flagged answer above, if you don't want to call DISTANCE function twice, refer to the alias in the WHERE clause, i.e:

SELECT *, DISTANCE(latitude, longitude, ?, ?) AS dist FROM COUNTRY c WHERE c.id NOT LIKE ? AND dist <= ? ORDER BY dist;