CODEWITHSUNDEEP
mysqlsqlsql-order-by
max_
max_

Reputation: 24481

Use a second ORDER by in MySQL query

I am using the following query to select the nearest rows to a specified latitude and longitude. The results are then ordered by distance, returning the nearest rows out of the selected data set.

However, I would like to then order the returned nearest rows by the expiry_date field to order that data set so that I will have the ending soonest (nearest expiry date) at the top and furthest at the bottom.

Please can you tell me how I can do this?

SELECT * , ( 6371 * ACOS( COS( RADIANS( latitude ) ) * COS( RADIANS( 51.61062 ) ) * COS( RADIANS( - 0.236952 ) - RADIANS( longitude ) ) + SIN( RADIANS( latitude ) ) * SIN( RADIANS( 51.61062 ) ) ) ) AS distance
FROM  `questions` 
WHERE  `expiry_date` > 
CURRENT_TIMESTAMP HAVING distance <=50000
ORDER BY distance
LIMIT 0 , 15

Upvotes: 7

Views: 19466

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You need a subquery:

select t.*
from (<your query here>) t
order by expiry_date desc

Upvotes: 1

John Conde
John Conde

Reputation: 219804

ORDER BY can be a comma separated list. Just list them by order of presdence:

SELECT * , ( 6371 * ACOS( COS( RADIANS( latitude ) ) * COS( RADIANS( 51.61062 ) ) * COS( RADIANS( - 0.236952 ) - RADIANS( longitude ) ) + SIN( RADIANS( latitude ) ) * SIN( RADIANS( 51.61062 ) ) ) ) AS distance
FROM  `questions` 
WHERE  `expiry_date` > 
CURRENT_TIMESTAMP HAVING distance <=50000
ORDER BY distance
, expiry_date DESC
LIMIT 0 , 15

Upvotes: 35

Related Questions