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 the listings with matching description sorted by timestamp.

Answer 1

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

Answer 2

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

Answer 3

(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)

Answer 4

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

Answer 5

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)