MySQL - Rounding (sort of) - Want to round number only if greater than/less than 10

Question

My query looks like this:

SELECT id, name, 
MATCH (name) AGAINST (%serach term%) as relevance,
address 
FROM table

I'm getting relevance results ranging betweeen 5.1 and 14.0. I want to sort by relevance, but also by another field, so I'd like to round the relevance a bit.

I got something working like this:

SELECT id, name, 
round(MATCH (name) AGAINST (%serach term%),0) as relevance,
address 
FROM table

This rounds it to the nearest whole number.

But, ideally, I'd really like to know which are above 10 and which are below 10 and have that be the only distinguishing factor.

So: 1, 5.1, 9.9 would all 'round' to 10. Then, 10.1, 14.1, would all 'round' to 20.

Or it could be 0 and 1. Or 0 and 10, or 0 and 20.

I'm not sure how to go about this, or if it's even possible. I've been looking at ceiling() and round() but I can't quite figure it out.

Answer 1

Try CASE WHEN

SELECT id, name, 
CASE WHEN MATCH (name) AGAINST (%serach term%) <= 10 THEN 10 
     WHEN MATCH (name) AGAINST (%serach term%) > 10 THEN 20
     END as relevance,
address 
FROM table