sorting on an expression in a recordset

Results 1 to 2 of 2

Thread: sorting on an expression in a recordset

  1. #1
    stefan Guest

    Default sorting on an expression in a recordset

    I&#039m working on a little asp project ( my first actually ) .It is<BR>basically a little search engine that looks into a database filled with<BR>url&#039s and several descriptions of that url. The user can enter some<BR>keywords to search for and the records that match these keywords on<BR>several fields in the database are returned.<BR><BR>I&#039d like to sort the results on relevance though but I don&#039t know how to<BR>do it.<BR><BR>1) what is a good measure of relevance : I thought to use the number of<BR>matches with the keywords; the greater the number of matches, the more<BR>relevant the link.<BR><BR>2)<BR>&#062; sql , as far as I know can only sort on an existing column or columns<BR>and of course the number of matches is not part of the database<BR>&#062; when I have a recordset with the results I can only sort on an<BR>existing column , not on an expression or something<BR><BR>so how do I sort on the number of matches ( or another measure or<BR>relevance )<BR><BR>the answer seems to be to put the recordset in an array , calculate the number of hits , put in in the array and sort the array , but i&#039m still far off from actual code ; <BR><BR>And how about paging. the asp is set up so that I can page through the results. Can I still page now ?<BR><BR>does anybody have some answers and maybe some sample code?

  2. #2
    Join Date
    Dec 1969

    Default Relational DB poor for relevance ranking

    Your idea of putting the results into an array and counting would *seem* to be the right way...but it won&#039t work.<BR><BR>Consider this query:<BR><BR>SELECT * FROM urlTable <BR>WHERE info LIKE &#039%words%&#039 <BR>OR info LIKE &#039%entered%&#039 <BR>OR info LIKE &#039%by%&#039 <BR>OR info LIKE &#039%user%&#039<BR><BR>A record that contained an info field "these are the same words entered by the user" would *still* only show one "hit" for that record. That is, the record would only be put into the result set one time.<BR><BR>So now you are stuck duplicating a lot of the same work the DB did in the query in your VBS code.<BR><BR>That is, you&#039ll need to do something like this:<BR><BR>&#060;%<BR>keywords = Request.Form("keywords") &#039 whatever is entered on prior page<BR><BR>words = Split( keywords ) &#039 split on spaces<BR><BR>where = "info LIKE &#039%" & Join( words, "%&#039 OR info LIKE &#039%" ) & "%&#039"<BR><BR>&#039 study that one! It produces a string of the form<BR>&#039 &nbsp; &nbsp; info LIKE &#039%word1%&#039 OR info LIKE &#039%word2%&#039<BR>&#039<BR>&#039 so make that (part of?) the WHERE clause in SQL:<BR>SQL = "SELECT url, info FROM urlTable WHERE " & where<BR><BR>&#039 now get a RecordSet that matches that query:<BR>Set RS = yourConnection.Execute( SQL ) <BR><BR>&#039 convert the RS into an array in one call!<BR>allRows = RS.GetRows <BR><BR>&#039 make an array to hold relevancy numbers<BR>Dim relevancy( )<BR>ReDim relevancy( UBound( allRows, 2 ) )<BR><BR>&#039 now go do ranking<BR>For rNum = 0 To UBound( allRows, 2 )<BR>&nbsp;&nbsp;&nbsp; info = allRows( 1, rNum ) &#039 note that "info" is field #1 in SELECT<BR>&nbsp;&nbsp;&nbsp; count = 0<BR>&nbsp;&nbsp;&nbsp; For wNum = 0 To UBound( words )<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; curWord = words( wNum )<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; start = InStr( 1, info, curWord, vbTextCompare )<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Do While start &#062; 0<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; count = count + 1<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; start = InStr( start, info, curWord, vbTextCompare )<BR>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Loop<BR>&nbsp;&nbsp;&nbsp; Next &#039 to next word in array of words<BR>&nbsp;&nbsp;&nbsp; relevancy( rNum ) = count<BR>Next &#039 to next record in array<BR>%&#062;<BR><BR>Enough...I guess that does what you suggested. But boy is it going to be slow and clumsy. Not something you want to use on a crowded site!<BR><BR>If you are serious about this, you&#039ll probably want to use a text search engine instead of a relational DB. You *can* use SQL Server 7 for this purpose, I should note. I have no idea how its performance compares with a "real" text engine.<BR><BR><BR><BR>

Posting Permissions

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