How to weight 'ORDER BY' in mysql?

mysql, sql, sql-order-by

Solution

This should work

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

DEMO

Update from comments

to make top 3 results sort by click_rate, and the rest sort by relevance

You'd need to first identify the first 3 using a subquery and do the ordering

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

Problem

I have a full text query that ends with: ``` ORDER BY RELEVANCE DESC, CLICK_RATE DESC ``` Can I give weight to the columns in the `order by`? Maybe `0.3` to relevance and `0.7` to `click_rate`? As things are right now, even if I switch them around, the results are not satisfactory. As an alternative how can have the top `3` results sorted by `CLICK RATE` and the rest sorted by `relevance`.

Original source

Related problems