Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: how to sort by most keyword found first

    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.
    Compare bible texts (and other tools):
    TheWheelofGod

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Posts
    494
    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
    Compare bible texts (and other tools):
    TheWheelofGod

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it assigns the alias "d" to the derived table i.e. subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2004
    Posts
    494
    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?
    Compare bible texts (and other tools):
    TheWheelofGod

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •