Relevance-based Site Search with SQL | JarrodGoddard.com

SELECT title, filename, sum(relevance)
FROM (
SELECT title, filename, 10 AS relevance FROM page WHERE title LIKE ‘%about%’
UNION
SELECT title, filename, 7 AS relevance FROM page WHERE filename LIKE ‘%about%’
UNION
SELECT title, filename, 5 AS relevance FROM page WHERE keywords LIKE ‘%about%’
UNION
SELECT title, filename, 2 AS relevance FROM page WHERE description LIKE ‘%about%’
) results
GROUP BY title, filename
ORDER BY relevance DESC;