Conditional order by sorting using two fields in MySQL

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.

Share on Facebook

Tweet

Follow us

Save