View Full Version : SQL Query - Order By Relevance

11-30-2004, 04:37 PM
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();

11-30-2004, 04:54 PM
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

11-30-2004, 05:11 PM
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?

11-30-2004, 06:28 PM
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.

11-30-2004, 07:41 PM
I think you're right. Do you have any that can sort relevance?

11-30-2004, 08:35 PM
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.

11-30-2004, 09:31 PM
Thank you for your input. I'll see what I can come up with using your idea.