SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot soapergem's Avatar
    Join Date
    Mar 2005
    Location
    Madison, WI
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL - order by relevance

    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:
    Code:
    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot soapergem's Avatar
    Join Date
    Mar 2005
    Location
    Madison, WI
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, thanks. MySQL is so cool.

  4. #4
    SitePoint Zealot soapergem's Avatar
    Join Date
    Mar 2005
    Location
    Madison, WI
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, I tried this, as suggested:
    Code:
    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:
    Quote 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.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you have limit in there twice. the limit clase comes after order by, so get rid of the first one.

  6. #6
    SitePoint Zealot soapergem's Avatar
    Join Date
    Mar 2005
    Location
    Madison, WI
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. It works now.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Feb 2004
    Location
    Boston, MA
    Posts
    188
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  9. #9
    SitePoint Zealot soapergem's Avatar
    Join Date
    Mar 2005
    Location
    Madison, WI
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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;
    Last edited by soapergem; Apr 6, 2006 at 02:07.


Bookmarks

Posting Permissions

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