<p>I have a query:</p>

<pre><code>SELECT *
FROM Items
WHERE column LIKE '%foo%'
   OR column LIKE '%bar%'
</code></pre>

<p>How do I order the results?</p>

<p>Let's say I have rows that match 'foo' and rows that match 'bar' but I also have a row with 'foobar'.</p>

<p>How do I order the returned rows so that the first results are the ones that matched more LIKEs? </p>

[{'answer_id': 79375, 'author': 'Milan Babuškov', 'author_id': 14690, 'author_profile': 'https://Stackoverflow.com/users/14690', 'pm_score': 0, 'selected': False, 'text': '<p>Which DBMS?</p>\n\n<p>It can be done via CTE or Union for example, but if you are using, for example, MySQL, then you can forget about it.</p>\n'}, {'answer_id': 79393, 'author': 'Vinko Vrsalovic', 'author_id': 5190, 'author_profile': 'https://Stackoverflow.com/users/5190', 'pm_score': 2, 'selected': False, 'text': "<p>Case or the kind of conditional construct your RDBMS supports is a way to do it</p>\n\n<pre><code>select *, case when col like '%foo%' and col like '%bar%' then 2 end \nelse 1 end as ordcol \nfrom items \nwhere col like '%foo%' or col like '%bar%' order by ordcol\n</code></pre>\n"}, {'answer_id': 79395, 'author': 'Booji Boy', 'author_id': 1433, 'author_profile': 'https://Stackoverflow.com/users/1433', 'pm_score': 0, 'selected': False, 'text': "<p>Try this code:</p>\n\n<pre><code>SELECT * FROM Items WHERE column LIKE '%foo%' OR column LIKE '%bar%'\norder by (select count(*) from items i where i.column= item.column) DESC \n</code></pre>\n\n<p>You could also group by <code>column</code> and <code>count(*)</code> then <code>ORDER</code>, if you don't care about the details.  </p>\n"}, {'answer_id': 79418, 'author': 'Mike Tunnicliffe', 'author_id': 13956, 'author_profile': 'https://Stackoverflow.com/users/13956', 'pm_score': 0, 'selected': False, 'text': "<p>You might want to give this a go:</p>\n\n<pre><code>SELECT *\nFROM Items\nWHERE column LIKE '%foo%' OR column LIKE '%bar%'\nORDER BY CASE WHEN column LIKE '%foo%' AND column LIKE '%bar%' THEN 1 ELSE 0 END DESC\n</code></pre>\n\n<p>Note: this is drycoded and probably not very portable.</p>\n"}, {'answer_id': 79422, 'author': 'Thomas', 'author_id': 14637, 'author_profile': 'https://Stackoverflow.com/users/14637', 'pm_score': 1, 'selected': False, 'text': '<p>You could use a <code>UNION</code>:</p>\n\n<pre><code>SELECT * FROM Items WHERE column LIKE \'%foo%\' AND column LIKE \'%bar%\'\nUNION\nSELECT * FROM Items WHERE column LIKE \'%foo%\' AND NOT (column LIKE \'%bar%\')\nUNION\nSELECT * FROM Items WHERE column LIKE \'%bar%\' AND NOT (column LIKE \'%foo%\');\n</code></pre>\n\n<p>But this may be bad performance-wise. Worse, I\'m guessing that you want to use this to construct a search engine that gives the most meaningful results first, and then the number of words does not remain limited to 2.</p>\n\n<p>In that case, you could create a <code>score</code> column which contains the number of matches. Something like this:</p>\n\n<pre><code>SELECT\n    *,\n    (IF(column LIKE \'%bar%\', 1, 0) + IF(column LIKE \'%foo%\', 1, 0)) AS score\nFROM Items\nWHERE column LIKE \'%foo%\' OR column LIKE \'%bar%\'\nORDER BY score DESC;\n</code></pre>\n\n<p>My SQL is a bit rusty, but something like this should be possible in at least MySQL 5.0. See also the manual for the <code>IF</code> function:\n<a href="http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html</a></p>\n'}, {'answer_id': 79430, 'author': 'nicudotro', 'author_id': 14635, 'author_profile': 'https://Stackoverflow.com/users/14635', 'pm_score': 2, 'selected': False, 'text': "<pre><code>SELECT * FROM Items WHERE column LIKE '%foo%' OR column LIKE '%bar%' \nORDER BY \n(IF(column LIKE '%foo%',1,0) + IF(column LIKE '%bar%',1,0)) \nDESC\n</code></pre>\n\n<p>The syntax for if is </p>\n\n<p><code>IF ( condition, true_value, false_value )</code></p>\n"}, {'answer_id': 79473, 'author': 'Jolyon', 'author_id': 11740, 'author_profile': 'https://Stackoverflow.com/users/11740', 'pm_score': 0, 'selected': False, 'text': "<p>2 Queries:  </p>\n\n<pre><code>SELECT * FROM Items WHERE column LIKE '%foo%' AND column LIKE '%bar%';<br/>\nSELECT * FROM Items WHERE (column LIKE '%foo%' AND column NOT LIKE '%bar%') OR (column NOT LIKE '%foo%' AND LIKE '%bar%')</code></pre>\n\n<p>(No XOR in SQL)</p>\n"}, {'answer_id': 79497, 'author': 'Mark Nold', 'author_id': 4134, 'author_profile': 'https://Stackoverflow.com/users/4134', 'pm_score': 0, 'selected': False, 'text': '<p>Not all RDBMS support IF (or DECODE in Oracle) statements. If not you could use a subquery to define table "a" and search for all employee\'s named JO SMITH or a combination.</p>\n\n<pre><code>SELECT \n a.employee_id,\n a.surname,\n sum(a.counter)\nFROM\n\n (SELECT\n   employee_id,\n   surname,\n   1 as counter\n  FROM\n   MyTable\n  WHERE\n   surname like \'%SMITH%\'\n\n  UNION ALL\n\n  SELECT\n   employee_id,\n   surname,\n   1 as counter\n  FROM\n   MyTable\n  WHERE\n   surname like \'%JO%\'\n   ) a\n\nGROUP BY \n a.employee_id,\n a.surname\nORDER BY 3,1,2\n</code></pre>\n\n<p>Make sure you use UNION ALL otherwise it will not work. Also you may way to use UPPER() to make your search non-case sensitive.</p>\n'}, {'answer_id': 79598, 'author': 'Matt', 'author_id': 4154, 'author_profile': 'https://Stackoverflow.com/users/4154', 'pm_score': 0, 'selected': False, 'text': '<p>As your query is currently written, the WHERE clause will not give you any information that can be used to sort your results.  I like <a href="https://stackoverflow.com/questions/79367/sql-text-searching-and-ordering#79380">Brian\'s idea</a>; add a constant column and UNION the queries and you could even get everything in one result set.  For example:</p>\n\n<pre><code>SELECT 1 as rank, * FROM Items WHERE column LIKE \'%foo%\' AND column LIKE \'%bar%\'\nUNION\nSELECT 2 as rank, * FROM Items WHERE column LIKE \'%foo%\' AND column NOT LIKE \'%bar%\'\nUNION\nSELECT 2 as rank, * FROM Items WHERE column LIKE \'%bar%\' AND column NOT LIKE \'%foo%\'\nORDER BY rank\n</code></pre>\n\n<p>However, this would only give you something like this:</p>\n\n<ul>\n<li>The unordered set of all rows that match foo and match bar</li>\n<li>followed by (the unordered set of) all rows that match foo or bar, but not both (although you could break this up into two separate groups using a different constant in the last SELECT statement).</li>\n</ul>\n\n<p>Which might be just what you\'re looking for, but it wouldn\'t tell you which rows matched foo three times, or sort them ahead of rows that only contained one instance of foo.  Also all those LIKEs can get expensive.  If what you\'re really looking to do is sort results based on relevance (however you define that) you might be better off using a full text index.  If you\'re using MS SQL Server, it has a built-in service that will do this, and there are also third-party products that will do the same.</p>\n\n<p>EDIT: After looking at all the other answers (there were only <em>two</em> when I started mine - I\'m obviously going to have to learn to think faster ;-) ) it\'s obvious that there are several ways to go about this, depending on exactly what you\'re trying to accomplish.  I would advise you to test and compare solutions based on how they perform <strong>on your system</strong>.  I\'m not a performance/tuning expert, but functions tend to slow things down, especially if you\'re sorting on the result of a function.  The LIKE operator isn\'t necessarily spry, either.  As a developer, it seems natural to use familiar constructs like "IF" and "CASE", but queries that use more of a set-based approach usually have better performance in a RDMS.  Again, YMMV, so it\'s best to test if you\'re at all concerned about performance.</p>\n'}, {'answer_id': 81772, 'author': 'Andy Irving', 'author_id': 8553, 'author_profile': 'https://Stackoverflow.com/users/8553', 'pm_score': 1, 'selected': False, 'text': "<pre><code>SELECT * FROM Items\nWHERE col LIKE '%foo%'\n    OR col LIKE '%bar%'\nORDER BY CASE WHEN col LIKE '%foo%' THEN 1\n                WHEN col LIKE '%bar%' THEN 2\n            END\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/79367', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']