CODEWITHSUNDEEP
mysqlsqlsql-order-by
califmerchant
califmerchant

Reputation: 167

How to weight 'ORDER BY' in mysql?

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.

Upvotes: 8

Views: 3957

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52645

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

Upvotes: 12

MaxGenius
MaxGenius

Reputation: 23

You can always use WHERE.

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

Upvotes: -4

Related Questions