sql server - SQL Search for multple words and sort results by relevance -

sql server - SQL Search for multple words and sort results by relevance -

hope simple of database gurus.

i’ll seek short , concise possible.

i’m not sql need query (using sql server 2008) following:

let’s there products table columns (simplified):

id, title, description, brandid, typeid, cost

tasks:

return rows matching brandid return rows matching typeid return rows price on or equal value need implement sql paging e.g. between send parameters for: start row, end row , page size. results sorted price (asc or desc) , title (also asc or desc)

so far nil much complicated , have solution.

for example:

select t.* ( select count(1) over() totalrecords, row_number() over(order product.price asc) rownumber, product.* product products.brandid = @brandid , products.typeid = @typeid , products.price >= @price ) t t.rownumber between @startrownumber , @endrownumber

now fun starts: need add together 1 more criteria search , keywords multiple words (separated space) , should match them against title , description columns , in case order by relevance relevance number of matched words within title column results title match of words keywords returned first. description column ignored when counting relevance, needs match @ to the lowest degree 1 word keywords like ‘%’ + @word ‘%’

of course, keywords split @words not problem.

one of import note: using total text search appropriate solution in case can’t utilize it!

any help appreciated.

thanks in advance!

sql-server