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, Oct 11 '12 at 04: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, Oct 11 '12 at 04: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, Oct 11 '12 at 06:37
Could you post the table structure? This could help troubleshoot further. — Seth, Oct 11 '12 at 17:41

Nikson Kanti Paul · Answer 1 · 2012-10-11T06:44:12.950

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 '12 at 06:44

answered Oct 11 '12 at 05:24

Nikson Kanti Paul

3,394
1
35
51

1

Standard SQL might not, but MySQL allows referencing any aliases in HAVING. http://stackoverflow.com/questions/2905292/where-vs-having – Barmar Oct 11 '12 at 06:38
Also see the [MySQL documentation](http://dev.mysql.com/doc/refman/5.1/en/problems-with-alias.html) – Barmar Oct 11 '12 at 06:40

MySQL can't ORDER BY using HAVING

1 Answers1