Ordering FREETEXTTABLE result UNIONed with standard SELECT by Rank

Question

I had a working FREETEXTTABLE query that searched for a @searchString. I now need to UNION that with another simple query that tries to parse the @searchString into an INT, and if it succeeds, filtering the table by looking for the row with PK equal to the parse @searchString.

Previously, I could easily JOIN the FREETEXTTABLE result to the table it was searching, order by the Rank, but only SELECT the columns of the original table that was searched.

Now that I am combining the unique results between the text search query and the query looking for the row with the search string as the key, I no longer have access to the Rank from the text search query.

How can I maintain the ordering by rank of the full text search, but place the query result looking for the row with the primary key (if it has a result) BEFORE the full text search results?

Answer 1

Have you tried just adding a constant to your union that puts your exact match of the PK on the top of the list? I don't remember off the top of my head what the freetext RANK column returns (0 thru 1000 i think), but something like this would work assuming that you just make your constant higher than the top of the rank.

DECLARE @id int

IF ISNUMERIC(@myStringId) = 1
    SET @id = CAST(@myStringId AS int)
ELSE
    SET @id = 0

WITH MyFreetextCte as (SELECT   [Rank], 
                                [Key]
                       FROM     FREETEXTTABLE(...)
                       UNION
                       SELECT   1001, 
                                (SELECT MyBaseTable.PK FROM MyBaseTable WHERE PK = @id))
SELECT    *
FROM      MyFreetextCte JOIN MyBaseTable ON MyFreetextCte.[Key] = MyBaseTable.PK
ORDER BY  MyFreetextCte.Rank DESC

Answer 2

WIth a ton of help from Scott, this is what I have that finally works:

CREATE PROCEDURE dbo.testProcedure
(
    @searchPhrase nvarchar(500)
)

AS

DECLARE @id int
SET @id = 0;
BEGIN TRY
 SET @id = CAST(@id AS int)
END TRY
BEGIN CATCH 
END CATCH;
-- at this point, @id will be the primary key if it is only digits
-- otherwise it will default to zero (which is out of range of my identity PK)

WITH ftsTable AS (
 SELECT RANK, [KEY] FROM FREETEXTTABLE(sourceTable, *, @searchPhrase)
 UNION
 SELECT 1001, (SELECT sourceTableID FROM sourceTable WHERE sourceTableID = @id)
) 

SELECT sourceTable.*
FROM ftsTable JOIN sourceTable ON ftsTable.[KEY] = sourceTable.sourceTableID
ORDER BY ftsTable.RANK DESC