CODEWITHSUNDEEP
phpmysqlsqlsortingconditional-statements
adrianTNT
adrianTNT

Reputation: 4098

Mysql order by two columns if one is grater than timestamp

I have this SQL SELECT in PHP:

SELECT * 
  FROM ads 
 ORDER BY ad_sponsored_date DESC, ad_date DESC

I want to display a list of ads, they are normally sorted by date but if one is sponsored, then it appears in top. The above does that nicely; but I need to also test if that ad_sponsored_date is not grater than timestamp and if so, then only sort by the second one (ad_date) , is it possible?

Something like this but I don't know the correct syntax:

SELECT * 
  FROM ads 
 ORDER BY (ad_sponsored_date DESC IF ad_sponsored_date > $timestamp, ad_date DESC)

Upvotes: 0

Views: 280

Answers (2)

peterm
peterm

Reputation: 92815

Try

SELECT * 
  FROM ads 
  ORDER BY CASE WHEN ad_sponsored_date > '$timestamp'
                THEN ad_sponsored_date 
                ELSE ad_date END DESC

Here is SQLFiddle demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

This captures your logic in SQL:

SELECT *
FROM ads
ORDER BY (case when ad_sponsored_date > $timestamp then ad_sponsored_date end) desc,
         ad_date DESC;

I am, however, unclear on what to do for the "else" part of the if.

Note: in MySQL NULL values are placed last for a descending sort, which is why this puts the sponsored ads at the top of the list.

Upvotes: 1

Related Questions