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.

Answer 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;