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?

OMG Ponies · Accepted Answer · 2010-01-20T05:06:17.413

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;

edited Jan 20 '10 at 05:06

answered Jan 20 '10 at 04:59

OMG Ponies

325,700
82
523
502

But I did try specifying a `GROUP BY` clause... Is there any way I can run the query without having to call the `DISTANCE` function twice? It would make the query run considerably faster. – Alix Axel Jan 20 '10 at 05:03
Hummm... I'm not a big fan of nested queries either, page 8 of http://www.arubin.org/files/geo_search.pdf is much more straightforward. MySQL Manual (http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html) says "The SQL standard does not allow the HAVING clause to name any column that is not found in the GROUP BY clause if it is not enclosed in an aggregate function". Is this SQLite specific? Sorry for being a PITA but any other way to work around this? – Alix Axel Jan 20 '10 at 05:14
@Alix: There aren't any other options to minimizing the number of times you use `DISTANCE`. `DISTANCE` isn't an aggregate function, so comparison belongs in the `WHERE` clause. The only reason to use `HAVING` is because you are actually grouping records - which is unlikely for a table of countries. – OMG Ponies Jan 20 '10 at 05:23

score 1 · Answer 2 · edited Jan 25 '22 at 15:41

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.

Thanks. I'm doing that, I just didn't posted to avoid adding too much clutter. =) — Alix Axel, Jan 20 '10 at 22:06

score -1 · Answer 3 · answered Jan 20 '10 at 04:49

-1

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

answered Jan 20 '10 at 04:49

chirag

91
1
1
10

Thanks, I didn't knew that. Is there any way to rewrite the query so that it only returns records having `distance <= ?` without having to call the `DISTANCE` function twice? – Alix Axel Jan 20 '10 at 05:07

score -3 · Answer 4 · edited Feb 16 '12 at 21:21

-3

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;

edited Feb 16 '12 at 21:21

Taryn

242,637
56
362
405

answered Feb 22 '10 at 15:00

SimonDavies

5
1

Take a look at the last code snippet of my question... – Alix Axel Feb 22 '10 at 19:49

Trouble with SQLite SQL Query

4 Answers4

Linked