Order SharePoint Search Results By More Columns
I'm using a FullTextSqlQuery in SharePoint 2007 (MOSS) and need to order the results by two columns:
SELECT WorkId FROM SCOPE() ORDER BY Author ASC, Rank DESC
However it seems that only the first column from ORDER BY is taken into account when returning results. In this case the results are ordered correctly by Author, but not by Rank. If I change the order the results will be ordered by Rank, but not by Author.
I had to resort to my own sorting of the results, which I don't like very much. Has anybody a solution to this?
Edit: Unfortunately it also doesn't accept expressions in the ORDER BY clause (SharePoint throws an exception). My guess is that even if the query looks like legitimate SQL it is parsed somehow before being served to the SQL server.
I tried to catch the query with SQL Profiler, but to no avail.
Edit 2: In the end I used ordering by a single column (Author in my case, since it's the most important) and did the second ordering in code on the TOP N of the results. Works good enough for the project, but leaves a bad feeling of kludgy code.
Microsoft finally posted a knowledge base article about this issue.
"When using RANK in the ORDER BY clause of a SharePoint Search query, no other properties should be used"
Symptom: When using RANK in the ORDER BY clause of a SharePoint Search query only the first ORDER BY column is used in the results.
Cause: RANK is a special property that is ranked in the full text index and hence cannot be used with other managed properties.
Resolution: Do not use multiple properties in conjunction with the RANK property.
- → Licensing system for client side code web application
- → Can ASP.NET AJAX partial rendering work inside a SharePoint 2007 application page?
- → Windows Sharepoint Services (WSS) and Forms Authentication - Passing those credentials to other ASP.NET Forms Authentication Apps
- → Bypass invalid SSL certificate errors when calling web services in .Net
- → Errors creating WebPart subclass in another assembly
- → Do I have to use InfoPath forms for every task within a SharePoint workflow?
- → How do I check if a Sharepoint Document Library has the Require Approval flag set, using the Sharepoint API?
- → Jquery - JS Clear table before new search, whitin function or out?
- → Automatically open Office add-in on SharePoint Online
- → Knockout unable to process binding in a specific function
- → Reformat list to grouped structure