Here’s the scenario.
I have two fields in my “listings” table with help of which i wanted to sort (order by) results. First field is “activation_date”. It is the activation date of a listing. Second one is “featured” which stores featured tag for a listing in a value of 1 or 0. I wanted that the listings which are “featured” (featured=1) AND were activated withing last 14 days come at the top ordered by activation_date in descending order. After that come the remaining “active” listings ordered by activation_date in descending order. Here’s the sql statement which i ended with. (obviously those “featured” listings should be “active” as well)
SELECT * FROM 'listings` WHERE `active`=1 ORDERED BY CASE WHEN (DATEDIFF(CURRENT_DATE, `activation_date`) < 14) THEN featured ELSE 0 END DESC, activation_date DESC
I hope it helps someone.