Menu
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.
726
After Grouping the data, you can filter the grouped record using HAVING Clause. HAVING Clause returns the grouped records which match the given condition. You can also sort the grouped records using ORDER BY.
The ORDER BY clause is used to get the sorted records on one or more columns in ascending or descending order. The ORDER BY clause must come after the WHERE, GROUP BY, and HAVING clause if present in the query. Use ASC or DESC to specify the sorting order after the column name.
In the query, GROUP BY clause is placed before ORDER BY clause if used any. In the query , Group BY clause is placed before Having clause .
The HAVING clause must come after the GROUP BY clause and before the ORDER BY clause. The HAVING clause can include one or more conditions. The HAVING condition can only include columns that are used with the GROUP BY clause. To use other columns in the HAVING condition, use the aggregate functions with them.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
