SQL ORDER BY DESC not working with multiple ordering options?

Question

I need to select text from two columns but also order by pubdate desc.

"title LIKE ? AND description LIKE ?" works in two columns, and I get the result I expected, but "pubdate" is not ordering DESC.

I need to use title LIKE ? AND description LIKE ? + pubdate DESC

pubdate is a timestamp number.

pubdate ordering now: old, new, old, new, old

how i need to order: new, new, old, old, old

I tried this SQL query but it failed.

SELECT source,title,description,link,pubdate
FROM feeds
USE INDEX(PRIMARY,idx_title_description)
WHERE MATCH(title,description) AGAINST(? IN BOOLEAN MODE) 
ORDER BY title LIKE ? AND description LIKE ? AND pubdate DESC
LIMIT 60

Answer 1

1. ORDER BY clauses are structured like SELECT clauses, not like WHERE clauses. They have a comma-separated list of columns.
2. Expressions such as title LIKE ? yield the value 1 when true and 0 when false.

So, if you want to order your resultset by most recent date, but showing the matching ones first, you need.

ORDER BY (title LIKE ? AND description LIKE ?) DESC, pubdate DESC

The first DESC is there because you want the rows where (title LIKE ? AND description LIKE ?) is true -- has the value 1 -- first, then the ones where it's false -- 0 -- second.

This true / false :: 1 / 0 thing is a MySQL-specific hack. To be portable to other DBMS makes, you want

ORDER BY CASE WHEN title LIKE ? AND description LIKE ? THEN 1
              ELSE 0 END  DESC, 
         pubdate DESC

This puts all the matching rows first in your result set, then the non-matching ones. If you want them ordered by date, and the matching ones to show up first within each date, flip the order of the two items in the ORDER BY:

ORDER BY pubdate DESC, (title LIKE ? AND description LIKE ?) DESC