Is it possible to build following SQL query

Question

The original query looks like this (MySQL):

SELECT * 
FROM books 
WHERE title LIKE "%text%" OR description LIKE "%text%" 
ORDER BY date

Would it be possible to rewrite it (without unions or procedures), so that result will look like this:

• list of books where title matches query ordered by date, followed by:
• list of books where description matches query ordered by date

So basically just give a higher priority to matching titles over descriptions.

Answer 1

In sql server I would do the following:

select * from books 
where title like '%text%' or description like '%text%'
order by case when title like '%text%' then 1 else 2 end, date

I'm not sure if you can include columns in ORDER BY in mysql that aren't in the SELECT, but that's the principle I'd use. Otherwise, just include the derived column in the SELECT as well.