SQL Full Text Search Changing the result Order

Question

I am using Full Search text to implement search functionality. Basically I will have to do search on all the columns with the search string. Search string will have combination of values from different the columns. However I will not know what all columns will I have to search. For example, using the following query in full text SQL search to find customer based on Last Name, Address and Zip

DECLARE @SearchString VARCHAR(200)
SET @SearchString = 'Adam OR West OR 60608'

SELECT Id, First_Name, Last_Name, Address,City,State,Zip, Rank
FROM Patient_Ex PAT 
    INNER JOIN CONTAINSTABLE(Patient_Ex,*, @SearchString) CT 
        ON PAT.Id = CT.[Key]

And I am getting the below results.

Id  FirstName   LastName    Address City    State   Zip RankID
25  Adam Avenue Adam     North Albay Avenue Chicago IL  60619   272
26  Adam Avenue West    South Escaaba Avenue    Chicago IL  60620   272
27  Adam Avenue Adam     eclaire Avenue Chicago IL  60634   272
28  Adam Avenue West    Leamington Avenue   Chicago IL  60657   272
29  Adam Avenue Adam    North England Avenue    Chicago IL  60660   272
30  Adam Avenue West    West 59th Street    Chicago IL  60608   272
31  Adam Avenue Adam    West 21st Place Chicago IL  60608   272
32  Adam Avenue West    South Albay Avenue  Chicago IL  60655   272

However, I am expecting the result to be something like this below. If the same row has all the three search criteria matching I would like that to be displayed at the top. In this scenario, a record having Last name as Adam, Address containing West and Zip 60608 should be displayed as top records.

Id  FirstName   LastName    Address City    State   Zip RankID
30  Adam Avenue Adam    West 59th Street    Chicago IL  60608   272
31  Adam Avenue West    West 21st Place Chicago IL  60608   272
27  Adam Avenue Adam    eclaire Avenue  Chicago IL  60634   272
28  Adam Avenue West    Leamington Avenue   Chicago IL  60657   272
29  Adam Avenue Adam    North England Avenue    Chicago IL  60660   272
25  Adam Avenue West    North Albany Avenue Chicago IL  60619   272
26  Adam Avenue Adam    South Escanaba Avenue   Chicago IL  60620   272
32  Adam Avenue West    South California Avenue Chicago IL  60655   272

How do I modify the query to get the result similar to the above one.

Thanks, Santha

Answer 1

you need to use conditional ORDER BY here:

DECLARE @SearchString VARCHAR(200)
SET @SearchString = 'Adam OR West OR 60608'

SELECT Id, First_Name, Last_Name, Address,City,State,Zip, Rank
FROM Patient_Ex PAT 
    INNER JOIN CONTAINSTABLE(Patient_Ex,*, @SearchString) CT 
        ON PAT.Id = CT.[Key]
ORDER BY 
  CASE WHEN (Last_Name='Adam' AND Address like '%West%' AND Zip = '60608') THEN 0 ELSE 1 END,
  CT.Rank DESC

The above query will put records with Last_Name=Adam, Address containing the word 'West' and Zip=60608 on top of the result set. The rest of records will be sorted by the full-text search Rank returned from CONTAINSTABLE with highest ranked records on top but right after the records matching the above mentioned 3 criteria.

You said you don't know what columns you will have to search for. See if you can use the above code snippet as a basis for creation of dynamic SQL which would operate on the columns used in a particular search case.

HTH