MySQL - order by relevance

[soapergem]

I'm fairly new to SQL, and I'm wondering if there's any built-in way of ordering results by how well they matched a given query. For instance, let's say we have this query:

SELECT *
FROM `items`
WHERE `description` LIKE 'quick'
AND (
     `description` LIKE 'brown'
  OR `description` LIKE 'fox'
  OR `description` LIKE 'lazy'
  OR `description` LIKE 'dog'
)
LIMIT 0 , 30
ORDER BY ???

Then, if I had the following rows of data...

1. The quick brown dog jumped fox-like.
2. The quick brown fox jumped over the lazy dog.
3. The quick fox jumped over the Sears Tower.

...I would want them to be ordered {2, 1, 3} because that's pretty obviously the order of what best matches the query. I have no idea if this possible or not, but I have learned that often times other people have already thought of the cool features I'd like so and accordingly I find they're already implemented. So I'm crossing my fingers. Thanks!

[r937]

ORDER
BY case when `description` LIKE 'brown' then 1 else 0 end
+ case when `description` LIKE 'fox' then 1 else 0 end
+ case when `description` LIKE 'lazy' then 1 else 0 end
+ case when `description` LIKE 'dog' then 1 else 0 end
DESC

[soapergem]

Cool, thanks. MySQL is so cool.

[soapergem]

So, I tried this, as suggested:

SELECT *
FROM `items`
WHERE `description` LIKE 'quick'
AND (
  `description` LIKE 'brown'
  OR `description` LIKE 'fox'
  OR `description` LIKE 'lazy'
  OR `description` LIKE 'dog'
)
LIMIT 0 , 30
ORDER BY (
  (
    CASE WHEN `description` LIKE 'brown'
    THEN 1
    ELSE 0
    END
  ) + (
    CASE WHEN `description` LIKE 'fox'
    THEN 1
    ELSE 0
    END
  ) + (
    CASE WHEN `description` LIKE 'lazy'
    THEN 1
    ELSE 0
    END
  ) + (
    CASE WHEN `description` LIKE 'dog'
    THEN 1
    ELSE 0
    END
  )
) DESC
LIMIT 0, 30

...and wound up with this:

Originally Posted by phpMyAdmin

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY ((CASE WHEN `description` LIKE 'brown'
THEN 1
ELSE 0
EN

I got the same error with or without the parentheses, and I think I prefer them there for clarity, so that's why they're there. Thanks again.

[longneck]

you have limit in there twice. the limit clase comes after order by, so get rid of the first one.

[soapergem]

Thanks. It works now.

[r937]

you may also want to conside using wildcard characters in your LIKE strings, because otherwise you are going to return precious little (i.e. only those specific 1-word descriptions)

[binjured]

Wow, I feel kind of dumb saying, but as a PHP/MySQL developer I didn't even realize this was possible! Not to derail this thread or anything (well, it is kinda concluded anyway), but could any of you SQL gurus suggest places to read more about "advanced" queries / techniques like this that don't involve just reading through the entire MySQL manual? Thanks!

[soapergem]

As far as using wildcard characters, r937, I had no intention of doing so. I was just using that for the example.

I had posted another question here, but I just figured it out on my own. I wanted to select this relevance value, and this is how it's done:

SELECT *, ((CASE WHEN `description` LIKE '%brown%' THEN 1 ELSE 0 END) + (CASE WHEN `description` LIKE '%fox%' THEN 1 ELSE 0 END) + (CASE WHEN `description` LIKE '%lazy%' THEN 1 ELSE 0 END) + (CASE WHEN `description` LIKE '%dog%' THEN 1 ELSE 0 END)) AS relevance
FROM `items`
WHERE `description`LIKE '%quick%' AND (
  `description`LIKE '%brown%'
  OR `description` LIKE '%fox%'
  OR `description` LIKE '%lazy%'
  OR `description` LIKE '%dog%'
)
ORDER BY relevance DESC
LIMIT 0 , 30;