Sorting union queries in MySQL

Question

I am making a search feature for a Job listing website. For that I need to first show the listings with matching titles and then the job listings with matching description. Here is the query, I am using right now:

Example:

(SELECT * FROM `jobs` WHERE title LIKE '%java%developer%') 
UNION DISTINCT 
(SELECT * FROM `jobs` WHERE description LIKE '%java%developer%')

However, I also need to sort the results by the timestamp so as to show the latest results first. Like it should give the results with matching titles sorted by timestamp and then the listings with matching description sorted by timestamp.

Answer 1

i would probably write the query similar to:

  select *, ((title like '%…%')*2 + (description like '%…%')) as rank
    from jobs
   where title like '%…%'
      or description like '%…%'
order by rank desc, time desc

this way, rows where both title and description match will appear first, then title-matches, then description-matches. i haven't tested it, but usually mysql does a good job converting bool to int (true: 1, false: 0)