CODEWITHSUNDEEP
sql-serversearchranking
Sean Carlisle
Sean Carlisle

Reputation: 96

I am looking to rank search results in a SQL query

The query:

SELECT TOP 1000 t.columns
FROM dbo.teams as t
RIGHT JOIN fnParseString('Nugget,Tulsa',',') ps ON t.team_name LIKE '%'+ps.string+'%' OR t.nickname LIKE '%'+ps.string+'%'

This does return the results I want, but but the ordering isn't useful.

I may add more columns to the search.

How do I rank the rows based on accuracy and number of matched terms? I know it only needs to match one of the terms to be selected, is there a way to then count the number of terms that match the columns.

I see Need help with SQL for ranking search results but will the subselect work for an arbitrary number of search tokens

Upvotes: 0

Views: 1002

Answers (2)

APH
APH

Reputation: 4154

Non-dynamic option, for contrast. You'd have to add to the order by and the join as you add more columns to check. 

SELECT TOP 1000 t.columns
FROM dbo.teams as t
RIGHT JOIN fnParseString('Nugget,Tulsa',',') ps 
    ON t.team_name LIKE '%'+ps.string+'%' 
       OR t.nickname LIKE '%'+ps.string+'%'
order by 
    case when t.team_name LIKE '%'+ps.string+'%' then 1 else 0 + 
    case when t.nickname LIKE '%'+ps.string+'%' then 1 else 0 
    desc

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81990

If you are open to some dynamic SQL

Declare @SearchFor  varchar(max) ='Daily,Production,default'  -- Any comma delim string
Declare @SearchFrom varchar(150) ='OD'                        -- table or even a join statemtn
Declare @SearchExpr varchar(150) ='[OD-Title]'                -- Any field or even expression
Declare @ReturnCols varchar(150) ='[OD-Nr],[OD-Title]'        -- Any field(s) even with alias 

Set @SearchFor = 'Sign(CharIndex('''+Replace(Replace(Replace(@SearchFor,' , ',','),', ',''),',',''','+@SearchExpr+'))+Sign(CharIndex(''')+''','+@SearchExpr+'))'
Declare @SQL nvarchar(Max) = 'Select * from (Select Distinct'+@ReturnCols+',Hits='+@SearchFor+' From '+@SearchFrom + ') A Where Hits>0 Order by Hits Desc' 

Exec(@SQL)

Returns

OD-Nr   OD-Title                    Hits
3       Daily Production Summary    2
6       Default Settings            1

Upvotes: 1

Related Questions