Sorting union queries in MySQL

I am making a search feature for a Job listing website. For that I nee开发者_开发知识库d 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.

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)

Try this, replace timestamp by the name of your timestamp column

(SELECT *, 1 as unionsorting FROM `jobs` WHERE title LIKE '%java%developer%' ORDER BY timestamp desc)
UNION DISTINCT
(SELECT *, 2 as unionsorting FROM `jobs` WHERE description LIKE '%java%developer%' ORDER BY timestamp desc)
ORDER BY unionsorting

But doing 2 queries is probably faster (this has to be tested)

SELECT * FROM `jobs` WHERE title LIKE '%java%developer%' ORDER BY timestamp desc
SELECT * FROM `jobs` WHERE description LIKE '%java%developer%' ORDER BY timestamp desc

SELECT * 
FROM `jobs` 
WHERE (title LIKE '%java%developer%' OR description LIKE '%java%developer%')
ORDER BY (CASE WHEN title LIKE '%java%developer%' THEN 0 ELSE 1 END), timestamp_col DESC

Edited: to fix...

This is a little clunky, but some variation on it will work:

SELECT title, description, timestamp_column, min(rank) from 
(
    (SELECT *, 1 as rank FROM `jobs` WHERE title LIKE '%java%developer%') 
    UNION
    (SELECT *, 2 as rank FROM `jobs` WHERE description LIKE '%java%developer%')
) x
GROUP BY title, description, timestamp_column 
ORDER BY min(rank), timestamp_column DESC

This uses the GROUP BY clause in place of DISTINCT

(SELECT * FROM `jobs` A WHERE title LIKE '%java%developer%' ORDER BY A.colDateTime desc) 
UNION DISTINCT 
(SELECT * FROM `jobs` B WHERE description LIKE '%java%developer%' ORDER BY B.colDateTime desc)