Menu
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
419
If you want to select records from a table but would like to see them sorted according to two columns, you can do so with ORDER BY . This clause comes at the end of your SQL query.
However we can use multiple columns in ORDER BY clause. When multiple columns are used in ORDER BY, first the rows will be sorted based on the first column and then by the second column.
Syntax: SELECT * FROM table_name ORDER BY column_name; For Multiple column order, add the name of the column by which you'd like to sort records first. The column that is entered at first place will get sorted first and likewise.
SELECT name, credit_limit FROM customers ORDER BY 2 DESC, 1; In this example, the position of name column is 1 and credit_limit column is 2. In the ORDER BY clause, we used these column positions to instruct the Oracle to sort the rows.
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
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
