 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / September 2004Scaling Rank
Hello, i m using full text search .. but the problem is i want to present the result with very few results with the rank ... the rank as generated by the sql full text is only usefull when compared with the other ranks .. i want to scale it from 1 to 100.. any ideas on how to do this ... Nargis, I'm not exactly sure what you're trying to achieve. Could you provide a more specific example? If you had a FTS query that returns 100 rows, would you want the rank for each row to be 1 - 100? If not, how would you group the ranking results on a per query basis? You should also review the paragraph in the SQL Server 2000 BOL title "Full-Text Search Recommendations" that starts with "What is RANK and how is it determined when used with CONTAINSTABLE and FREETEXTTABLE predicates? " More detailed info is always helpful in understanding your question/request so we can better answer your question/request. Thanks, John > Hello, > i m using full text search .. but the problem is i want to present the > result with very few results with the rank ... the rank as generated by the > sql full text is only usefull when compared with the other ranks .. i want to > scale it from 1 to 100.. any ideas on how to do this ... John Wat i basically want is that the if my query returns 100 records all should be ranked from 1- 100 , i want to make sure that rank would always be 1-100.. and at the same time i want to be able to rank the results of differnt queries ....so max thing will not work with me if the max rank for one query is 40 and if a record has a rank = 39 and another query returns max rank 144 and one record has rank 60 if i use max method .. it rank for 39 would be greater than the rank for 60 .... Is not there any other way to restrict sql server to never generate ranks beyond 100.... or any other work around for it ...?? > Nargis, > I'm not exactly sure what you're trying to achieve. Could you provide a more [quoted text clipped - 17 lines] > to > > scale it from 1 to 100.. any ideas on how to do this ... Can you try the query I supplied again. It does the ordering and the normilization you are looking for. make sure you don't forget the final order by rank desc.  SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html > John > Wat i basically want is that the if my query returns 100 records all should [quoted text clipped - 31 lines] > > to > > > scale it from 1 to 100.. any ideas on how to do this ... Nargis, What you may want to do is setup a method for "Rank Assignment" via importing your CONTAINSTABLE or FREETEXTTABLE query results into a temp table (with order by RANK) and along with a new column "AssignedRank" and then starting at 100 assign each row a decreasing AssignedRank value and then return this value to the user as the "rank" values. You can use a Top_N_Rank value of 100 to limit the results to 100 rows. If this is not what you're looking for, could you provide a more specific example with the query results you expect? Thanks, John > John > Wat i basically want is that the if my query returns 100 records all should [quoted text clipped - 31 lines] > > to > > > scale it from 1 to 100.. any ideas on how to do this ... I'd try something like this: declare @max int select @max= max(rank) from text join containstable(texttable, textcol,'Microsoft',1) as search on search.[key]=text.pk select (rank*100/@max) from text join containstable(texttable, textcol,'Microsoft',100) as search on search.[key]=text.pk order by rank desc This will involve two hits on your SQL FTS catalog. SignatureHilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html > Hello, > i m using full text search .. but the problem is i want to present the > result with very few results with the rank ... the rank as generated by the > sql full text is only usefull when compared with the other ranks .. i want to > scale it from 1 to 100.. any ideas on how to do this ... |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.