mysql most relevant search results for numbers

Question

I am finding a lot of answers on most relevant searching for text results, but not much at all on numbers. What would be the best if there were a way for the MySQL query to return results in the most relevant way for number searches. It would order by exact matches first and branch out the variable until all results were given.

EXAMPLE: Variable = 54

Results = 54,55,53,56,52,57,51...

Is there such a way to do this that anyone knows, and keep it all within a single query? I imagine if I have to that I could put the query into a php loop and just increment the number that way, returning results as they were found. But that seems rather code intensive and would cause for some lag in the results.

I saw this thread, but I am not sure this answers my question.

Thanks in advance.

What are your MySQL data types? Is your search just a numeric search, or is it a text search that also searches for numeric values? — Shan Plourde, May 29 '11 at 04:59
The search is for an array of things, but the numbers happen to be INT values. — ImaginedDesign, May 31 '11 at 01:29
I am looking for a way for the data returned to be in relevance from the center out. so for instance if we are looking at the numbers 1,2,3,4,5 and I wanted to see the middle number which is 3, it would show the results that matched 3 first. Next would be all results that matched 2 and 4. Third would be the results of 1 and 5. 3 was the most relevant and 1 and 5 were the least relevant. — ImaginedDesign, May 31 '11 at 01:35

score 5 · Accepted Answer · answered May 29 '11 at 04:08

5

Perhaps ORDER BY ABS(column - 54)?

MySQL can't use an index for this, so it won't be particularly speedy, but it does what you want.

answered May 29 '11 at 04:08

Eevee

47,412
11
95
127

It works indeed. Unfortunately, it cannot be used when the column's data type is an UNSIGNED INT. – MC Emperor Sep 14 '11 at 14:00
Then use CAST(column AS SIGNED). – Eevee Sep 15 '11 at 18:27

score 0 · Answer 2 · answered May 29 '11 at 04:09

I think it would help if there was more information about your table. Are you talking about a number that's inside some text in a text or char field or are you talking about an actual numerical column?

If it's a numerical column why not just use a range? Like so:

SELECT number_field, ABS(number_field - $variable) as relevance 
 FROM table 
 WHERE number_field >= ($variable - 3) 
     AND number_field <= ($variable + 3)
 ORDER by relevance ASC, number_field DESC;

If $variable = 54 it should return the results you have above.

mysql most relevant search results for numbers

2 Answers2