Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Nov 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Query - Order By Relevance

    Is there a built-in SQL function that sorts by relevance?

    I've been trying to do this using my query and I'm getting an error stating that:

    Microsoft OLE DB Provider for SQL Server error '80040e14'

    'RELEVANCE' is not a recognized function name.

    This is my query:

    SELECT Article_Date, Headline, Headline, teaser, id, Rank, Headline_Photo, Headline_Photo_Alt, Video, Audio, Multimedia FROM Articles_e WHERE Visible = 1 AND '11/30/04' >= Publish_Date ORDER BY RELEVANCE();

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    not sure what you are asking for, but i think you want to use a full-text search. http://www.sqlteam.com/item.asp?ItemID=120
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    New Coder
    Join Date
    Nov 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a database that contains a bunch of articles. Each articles has a number of fields, namely a headline, a teaser and the body of the article.

    I'm trying to do a full text search of my database that will display relevant articles that match the criteria that I specify. For example, if I want to search for articles that contain the words Army, Transformation, and vehicle, then I want it to return the articles that contain those words and I want it to sort it by relevance.

    How do I do that? I've already written the query that I have above. My server doesn't seem to be recognizing he ORDER BY RELEVANCE function, and I'm not sure that it exists on my server.

    Can you help?

  • #4
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts
    Is there such a thing as "RELEVANCE" in the ANSI 92 guidelines? I always thought relevance was an algorithm that the developer / programmer needed to create.

  • #5
    New Coder
    Join Date
    Nov 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think you're right. Do you have any that can sort relevance?

  • #6
    Mega-ultimate member
    Join Date
    Jun 2002
    Location
    Winona, MN - The land of 10,000 lakes
    Posts
    1,855
    Thanks
    1
    Thanked 45 Times in 42 Posts
    Well, I've only done something like this once on my wish list web site (www.yourlistonline.com). I have a 3 cycle algorithm which works like this...

    The visitor enters a first name and a last name.

    first I create a "matches" array

    Next I query the database for exact match.
    Next I query for exact match on last name,
    then I query for exact match on first name,
    then I query for "LIKE%" matches on both first and last name.

    With each query, I add an entry to my matches array. If the name already exists, I increment a counter for that name. Then I just sort the array based on the highest number of hits (or counts). Seems to work well for me.

  • #7
    New Coder
    Join Date
    Nov 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your input. I'll see what I can come up with using your idea.


  •  

    Posting Permissions

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