how to sort by most keyword found first

gilgalbiblewhee · #1 (**permalink**) 08-13-08, 19:04

How do I sort something like this by most keyword found first?

Code:

SELECT * 
  FROM bible 
 WHERE 1=1 
   AND CASE WHEN text_data LIKE '%there%' 
            THEN 1 ELSE 0 END
     + CASE WHEN text_data LIKE '%house%' 
            THEN 1 ELSE 0 END
     + CASE WHEN text_data LIKE '%daughter%' 
            THEN 1 ELSE 0 END
      > 4

I read this:
http://r937.com/keyword_relevance.html
But I don't think any of the cases mentioned applies to what I'm looking for.

r937 · #2 (**permalink**) 08-13-08, 19:15

in order to sort by a column, you should put that column into the SELECT clause

Code:

SELECT *
  FROM (
       SELECT bible.* 
            , CASE WHEN text_data LIKE '%there%' 
                   THEN 1 ELSE 0 END
            + CASE WHEN text_data LIKE '%house%' 
                   THEN 1 ELSE 0 END
            + CASE WHEN text_data LIKE '%daughter%' 
                   THEN 1 ELSE 0 END
              AS relevance      
         FROM bible
        WHERE text_data LIKE '%there%'
           OR text_data LIKE '%house%'
           OR text_data LIKE '%daughter%'
      ) AS d 
 WHERE relevance > 4
ORDER
    BY relevance

by the way, it's going to be pretty difficult to reach a score greater than 4 if you're only scoring up to 3 keywords

gilgalbiblewhee · #3 (**permalink**) 08-13-08, 21:00

Quote:

Originally Posted by r937

in order to sort by a column, you should put that column into the SELECT clause

Code:

SELECT *
  FROM (
       SELECT bible.* 
            , CASE WHEN text_data LIKE '%there%' 
                   THEN 1 ELSE 0 END
            + CASE WHEN text_data LIKE '%house%' 
                   THEN 1 ELSE 0 END
            + CASE WHEN text_data LIKE '%daughter%' 
                   THEN 1 ELSE 0 END
              AS relevance      
         FROM bible
        WHERE text_data LIKE '%there%'
           OR text_data LIKE '%house%'
           OR text_data LIKE '%daughter%'
      ) AS d 
 WHERE relevance > 4
ORDER
    BY relevance

by the way, it's going to be pretty difficult to reach a score greater than 4 if you're only scoring up to 3 keywords:)

Oh yeah. That was taken from another thread of mine. The actual sequel has more words.
I don't understand the meaning of this:

Code:

AS d

r937 · #4 (**permalink**) 08-13-08, 21:20

it assigns the alias "d" to the derived table i.e. subquery

gilgalbiblewhee · #5 (**permalink**) 08-13-08, 21:53

Hmmm...so I have to repeat?
I guess the first time around is to find the keywords and the 2nd is to sort.

Code:

            , CASE WHEN text_data LIKE '%there%' 
                   THEN 1 ELSE 0 END
            + CASE WHEN text_data LIKE '%house%' 
                   THEN 1 ELSE 0 END
            + CASE WHEN text_data LIKE '%daughter%' 
                   THEN 1 ELSE 0 END
              AS relevance      
         FROM bible
        WHERE text_data LIKE '%there%'
           OR text_data LIKE '%house%'
           OR text_data LIKE '%daughter%'

And what does relevance do?

r937 · #6 (**permalink**) 08-13-08, 22:01

relevance is the name assigned to the column which contains the result of the calculation

i added the WHERE clause simply to make your query more efficient -- no sense including, or calculating the relevance for, a row that has none of the keywords at all

Internet Services

Web Development

Digital Marketing

Consumer Tech