4

I'm trying to compose an SQL SELECT query with multiple search words. But I want the result be ordered by number of words matches.

For example, let the search string is "red green blue". I want the results which contains all these three words on top, after that the results, which contains two of them, and at the end - only one word matches.

SELECT
    *
FROM
    table
WHERE
    (col LIKE '%red%') OR
    (col LIKE '%green%') OR
    (col LIKE '%blue%')
ORDER BY
    ?????

Thanks in advance!

Pavel Tzonkov
  • 264
  • 3
  • 9

2 Answers2

7
ORDER BY
(
CASE 
WHEN  col LIKE '%red%' THEN 1
ELSE 0
END CASE
+     
CASE 
WHEN  col LIKE '%green%' THEN 1
ELSE 0 
END CASE
+    
CASE 
WHEN  col LIKE '%blue%' THEN 1
ELSE 0
END CASE
)  DESC

If your DB vendor has IF, you can use it instead of CASE (e.g., for Mysql you can write IF (col LIKE '%red% , 1,0) + IF(....'

a1ex07
  • 36,826
  • 12
  • 90
  • 103
1

What platform are you using? if SQL Server, then it sounds like a Full Text Search archtecture would be your best fit.

http://msdn.microsoft.com/en-us/library/ms142583.aspx

Stuart Ainsworth
  • 12,792
  • 41
  • 46