MySQL can't ORDER BY using HAVING

Question

Can any one advise me of a problem im having with ordering results in mysql

The Problem

Can not order by any column other than distance

SELECT * , ( ( ( ACOS( SIN( ( '56.3168322' * PI( ) /180 ) ) * SIN( ( `lat` * PI( ) /180 ) ) + COS( ( '56.3168322' * PI( ) /180 ) ) * COS( ( `lat` * PI( ) /180 ) ) * COS( ( ( '-5.414989099999957' - `lng` ) * PI( ) /180 ) ) ) ) *180 / PI( ) ) *60 * 1.1515 * 1.609344 ) AS `distance` FROM `incidents` HAVING `distance` <=3 ORDER BY `distance` ASC LIMIT 0 , 30

When I try to order column based on date in the date_incident row for example

 SELECT * , ( ( ( ACOS( SIN( ( '56.3168322' * PI( ) /180 ) ) * SIN( ( `lat` * PI( ) /180 ) ) + COS( ( '56.3168322' * PI( ) /180 ) ) * COS( ( `lat` * PI( ) /180 ) ) * COS( ( ( '-5.414989099999957' - `lng` ) * PI( ) /180 ) ) ) ) *180 / PI( ) ) *60 * 1.1515 * 1.609344 ) AS `distance` FROM `incidents` HAVING `distance` <=3 ORDER BY `date_incidents` ASC LIMIT 0 , 30

In the above it does not sort but still returns results.

Any help would be great on this.

Be careful when using transcendental math functions (eg: SIN(x), COS(x), etc.) to calculate distances. They are very slow. For larger tables, performance will suffer. — NullUserException
– NullUserException, Commented Oct 11, 2012 at 4:50
HAVING implies that some sort of aggregation is going on, I believe. Should the HAVING be changed to WHERE distance <=3 instead? That may be the problem. I don't see any aggregation going on here. That may be the problem--with the distance field in the HAVING clause, it could be implying GROUP BY distance. — Geoff Montee
– Geoff Montee, Commented Oct 11, 2012 at 4:51
@Geoff_Montee WHERE can't be used with aliases in the same SELECT, it can only be used with columns and aliases in tables and subqueries. — Barmar
– Barmar, Commented Oct 11, 2012 at 6:37
Could you post the table structure? This could help troubleshoot further. — Seth
– Seth, Commented Oct 11, 2012 at 17:41

Nikson Kanti Paul · Accepted Answer · 2012-10-11 06:44:12Z

1

Standard SQL does not permit the HAVING clause to name any column not found in the GROUP BY clause unless it is enclosed in an aggregate function.but MySQL allows referencing any aliases in HAVING clause. ex:

SELECT name, AVG(age) AS a FROM tables GROUP BY name HAVING a > 50;

edited Oct 11, 2012 at 6:44

answered Oct 11, 2012 at 5:24

Nikson Kanti Paul

3,4381 gold badge37 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barmar Over a year ago

Standard SQL might not, but MySQL allows referencing any aliases in HAVING. stackoverflow.com/questions/2905292/where-vs-having

Barmar Over a year ago

Also see the MySQL documentation

Collectives™ on Stack Overflow

MySQL can't ORDER BY using HAVING

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related