SQL Text Searching, AND Ordering

Question

I have a query:

SELECT * FROM Items WHERE column LIKE '%foo%' OR column LIKE '%bar%'

how do I order the results?

lets say I have rows that match 'foo' and rows that match 'bar' but I also have a row with 'foobar'

how do I order the returned rows so that the first results are the ones that matched more LIKEs?

Thanks

Answer 1

Which DBMS?

It can be done via CTE or Union for example, but if you are using, for example, MySQL, then you can forget about it.

Answer 2

Case or the kind of conditional construct your RDBMS supports is a way to do it

select *, case when col like '%foo%' and col like '%bar%' then 2 end 
else 1 end as ordcol 
from items 
where col like '%foo%' or col like '%bar%' order by ordcol

Answer 3

SELECT * FROM Items WHERE column LIKE '%foo%' OR column LIKE '%bar%' order by (select count(*) from items i where i.column= item.column) DESC

you could also group by [column] and count(*) then order, if you don't care about the details

Answer 4

You might want to give this a go:

• SELECT * FROM Items WHERE column LIKE '%foo%' OR column LIKE '%bar%' ORDER BY CASE WHEN column LIKE '%foo%' AND column LIKE '%bar%' THEN 1 ELSE 0 END DESC

Note: this is drycoded and probably not very portable.

Answer 5

You could use a UNION:

SELECT * FROM Items WHERE column LIKE '%foo%' AND column LIKE '%bar%'
UNION
SELECT * FROM Items WHERE column LIKE '%foo%' AND NOT (column LIKE '%bar%')
UNION
SELECT * FROM Items WHERE column LIKE '%bar%' AND NOT (column LIKE '%foo%');

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...

In that case, you could create a "score" column which contains the number of matches. Something like this:

SELECT
    *,
    (IF(column LIKE '%bar%', 1, 0) + IF(column LIKE '%foo%', 1, 0)) AS score
FROM Items
WHERE column LIKE '%foo%' OR column LIKE '%bar%'
ORDER BY score DESC;

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 IF function: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

Answer 6

SELECT * FROM Items WHERE column LIKE '%foo%' OR column LIKE '%bar%' 
ORDER BY 
(IF(column LIKE '%foo%',1,0) + IF(column LIKE '%bar%',1,0)) 
DESC

The syntax for if is

IF ( condition, true_value, false_value )

Answer 7

Dear Ben,

2 Queries:

SELECT * FROM Items WHERE column LIKE '%foo%' AND column LIKE '%bar%';

SELECT * FROM Items WHERE (column LIKE '%foo%' AND column NOT LIKE '%bar%') OR (column NOT LIKE '%foo%' AND LIKE '%bar%')

(No XOR in SQL)

Answer 8

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.

SELECT 
 a.employee_id,
 a.surname,
 sum(a.counter)
FROM

 (SELECT
   employee_id,
   surname,
   1 as counter
  FROM
   MyTable
  WHERE
   surname like '%SMITH%'

  UNION ALL

  SELECT
   employee_id,
   surname,
   1 as counter
  FROM
   MyTable
  WHERE
   surname like '%JO%'
   ) a

GROUP BY 
 a.employee_id,
 a.surname
ORDER BY 3,1,2

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.

Answer 9

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 Brian's idea; add a constant column and UNION the queries and you could even get everything in one result set. For example:

SELECT 1 as rank, * FROM Items WHERE column LIKE '%foo%' AND column LIKE '%bar%'
UNION
SELECT 2 as rank, * FROM Items WHERE column LIKE '%foo%' AND column NOT LIKE '%bar%'
UNION
SELECT 2 as rank, * FROM Items WHERE column LIKE '%bar%' AND column NOT LIKE '%foo%'
ORDER BY rank

However, this would only give you something like this:

• The unordered set of all rows that match foo and match bar
• 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).

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.

EDIT: After looking at all the other answers (there were only two 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 on your system. 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.

Answer 10

SELECT * FROM Items
WHERE col LIKE '%foo%'
    OR col LIKE '%bar%'
ORDER BY CASE WHEN col LIKE '%foo%' THEN 1
       WHEN col LIKE '%bar%' THEN 2
      END