How is the weight of "ORDER BY" in mysql?

Question

I have a full-text query that ends with:

ORDER BY RELEVANCE DESC, CLICK_RATE DESC 

Can I give weight to columns in order by ? Maybe 0.3 for relevance and 0.7 for click_rate ?

As things are now, even if I switch them, the results are not satisfactory.

Alternatively, how can I sort the 3 best results by CLICK RATE and sort the rest by relevance .

Answer 1

This should work

ORDER BY (.3 * RELEVANCE) + (.7 * CLICK_RATE) DESC

Demo

Update from comments

sort results by 3 best by click_rate, and sort the rest by relevance

You will need to first determine the first 3 using a subquery and place an order

 SELECT test.id, test.relevance, test.click_rate, top_3_click_rate.id t3_id, top_3_click_rate.click_rate t3_click_rate FROM test LEFT JOIN (SELECT id, click_rate FROM test ORDER BY click_rate DESC LIMIT 3) top_3_click_rate ON test.id = top_3_click_rate.id ORDER BY top_3_click_rate.click_rate DESC, test.relevance DESC 

Demo

Answer 2

You can always use WHERE.

Try the following: WHERE RELEVANCE> 0.3 AND RELEVANCE <0.7 ORDER BY RELEVANCE DESC, CLICK_RATE DESC