Order SharePoint search results by more columns

Question

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.

Answer 1

I have no experience in SharePoint, but if it is the case where only one ORDER BY clause is being honored I would change it to an expression rather than a column. Assuming "Rank" is a numeric column with a maximum value of 10 the following may work:

SELECT WorkId FROM SCOPE() ORDER BY AUTHOR + (10 - Rank) ASC

Answer 2

Rank is a special column in MOSS FullTextSqlQuery that give a numeric value to the rank of each result. That value will be different for each query, and is relative to the other results for that particular query. Because of this rank should have a unique value for each result, and sorting by rank then author would be the same as just sorting by rank. I would try sorting on another column instead of rank to see if results come back as you expect, if so your trouble could be related to the way MOSS is ranking the results, which will vary for each unique query.

Also you are right, the query looks like SQL, but it is not the query actually passed to the SQL server, it is special Microsoft Enterprise Search SQL Query syntax.

Answer 3

I, too, am experiencing the same problem with FullTextSqlQuery and MOSS 2007 where only the first column in a multi-column "ORDER BY" is respected.

I entered this topic in the MSDN Forums for SharePoint Search, but have not received any replies:

http://social.msdn.microsoft.com/Forums/en-US/sharepointsearch/thread/489b4f29-4155-4c3b-b493-b2fad687ee56

Answer 4

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"

http://support.microsoft.com/kb/970830

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.