How to write MySQL full-text query with MATCH(), ROUND() & Subquery?

Question

Currently, I am using this query, which does a pretty good job of finding the most relevant results and ordering by "most relevant" first:

  SELECT *,
         MATCH(text) AGAINST('$keyword') AS score
    FROM table_name 
   WHERE MATCH(text) AGAINST('$keyword')
ORDER BY score DESC 
   LIMIT $s, 20

Which produces results like:

text        |  score    |  date
---------------------------------------
hotdogs     |  13.2345  |  Dec 5, 2005
hamburgers  |  13.0765  |  July 4, 2008
pizza       |  8.0987   |  Oct 7, 2006

Now I am trying to rewrite the query to round the scores to whole numbers, and then sort those by date DESC. So the goal is to get results like:

text        |  score  |  date
---------------------------------------
hamburgers  |  13     |  July 4, 2008
hotdogs     |  13     |  Dec 5, 2005
pizza       |  3      |  Oct 7, 2006

I'm guessing I need to use some mix of ROUND(score), GROUP BY score and then ORDER BY date DESC -- but I can not figure out how to add that in successfully. I have tried numerous subqueries, but I can not get it to work. Any ideas? Thank you in advance for any pointers!

Answer 1

Your date has to be type DATE, otherwise you can't sort on date, impossible.

SELECT 
  text,
  ROUND(MATCH(text) AGAINST('$keyword')) AS score,
  date
FROM 
  table_name 
WHERE 
  MATCH(text) AGAINST('$keyword')
ORDER BY 
  score DESC,
  date DESC 
LIMIT 20
OFFSET $s