Ranking Multi Field DB Searches

[wdmny]

I am searching through multiple fields in my database and I need to return my results in descending relevance. How can I do this using ASP +/ SQL? A shortened form of my current statement would be like this:

SELECT * FROM TableName WHERE (Experience LIKE '%web%') OR (Experience LIKE '%design%') OR (Qualifications LIKE '%web%') OR (Qualifications LIKE '%design%')

I need to order my recordset starting with the record containing the most matches then down.

Thanks

[Abstraction]

What defines relavance?

[DaveMaxwell]

The best way would be to create a temp table and build counts in there, then return all the rows based on the count in the temp table. Here is a set of code which I think will do what you want....

dim keywords, SearchString, SearchWord

' Create the table.....
strSQL = "CREATE TABLE TempTable (MemberID, TimesFound)"
my_Conn.Execute strSQL

SearchString = ""
keywords = split(Request.Form("Search"), " ")
For Each word in keywords
	if instr(1, SearchString, word) > 0 then
		' Do Nothing - Word already searched for....
	else
		SearchWord = Replace(word, "'", "''")
		SearchWord = HTMLEncode(SearchWord)
		SearchString = SearchString & SearchWord

		SelectMembers(1)		' Search for qualifications first....
		SelectMembers(2)		' Search for experience next....
	end if
next

' Select the records based on the temp table
StrSql = "SELECT * From TableName WHERE MemberID = " & _
         " (SELECT MemberID from TempTable ORDER BY TimesFound)"
rs.Open strSql, my_Conn, 3,1
if rs.EOF or rs.BOF then
	' Do Nothing - no records found
else
	do until rs.EOF
			' Do your display code here....
		rs.MoveNext
	loop
end if
set rs = nothing 
rs.close

' Drop the table when done...
strSQL = "DROP TABLE TempTable"
my_Conn.Execute strSQL

Function SelectMembers(type)
	StrSQL = "SELECT Distinct MemberID FROM TableName WHERE "
	If Type = 1 then
		StrSQL = StrSQL & " Experience "
	Else
		StrSQL = StrSQL & " Qualifications "
	End if
	rs.Open strSql, my_Conn, 3,1
	if rs.EOF or rs.BOF then
		' Do Nothing - no records found
	else
		do until rs.EOF
			MemberID = rs("MemberID")
			StrSQL = "SELECT Count(*) as NumCount from TempTable where MemberID = " & MemberID
			set rsCount = my_Conn.Execute (strSql)
			if rsCount.BOF or rsCount.EOF then
				' No Subscriptions found, do nothing
			else
				If rsCount("NumCount") = 0 then
					strSQL = "INSERT INTO TempTable (MemberID, TimesFound) " & _
					         " VALUES (" & MemberID & ", 0)
				else
					strSql = "UPDATE TempTable SET TimesFound = TimesFound + 1 " & _
					         " WHERE MemberID = " & MemberID
				end if
				my_Conn.Execute(strSql)
			end if
			rsCount.Close
			set rsCount = nothing
			rs.MoveNext
		loop
	end if
	set rs = nothing 
	rs.close
End Function

[wdmny]

Thanks Dave, I just got that code adapted to my needs today. I also learned some things I didn't know in those SQL statements.

[DaveMaxwell]

Glad I could help!!!!