How to list row according to the relevance of the keywords searched for

Hi

Does anyone know "How to sort values return from an SQL query according to the relevance of the keywords" like the google does.

Thank you..

1. You can store the result in temp table/table variable then apply sorting.

2. You can use the derived quires

3. You can do it on the UI itself (if you use ASP.NET grid controls supports that)

|||

If you use full-text search in SQL Server you could use CONTAINSTABLE and FREETEXTTABLE functions instead of CONTAINS and FREETEXT predicates.

These functions are table-value function and one of returned columns is RANK.

This is example from BOL:

Code Snippet

SELECT FT_TBL.Description,
FT_TBL.CategoryName,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
CONTAINSTABLE (Categories, Description,
'("sweet and savory" NEAR sauces) OR
("sweet and savory" NEAR candies)'
) AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 2
AND FT_TBL.CategoryName <> 'Seafood'
ORDER BY KEY_TBL.RANK DESC;
GO

|||can free text search be applied to a column with datatype 'varchar'?|||

It can be applied, but first you must create full-text catalog and full-text index.

See following sample from BOL:

Code Snippet

USE AdventureWorks;GOCREATE UNIQUE INDEX ui_ukJobCand ON HumanResources.JobCandidate(JobCandidateID);CREATE FULLTEXT CATALOG ft AS DEFAULT;CREATE FULLTEXT INDEX ON HumanResources.JobCandidate(Resume) KEY INDEX ui_ukJobCand;GO

Now you could use FREETEXT for Resume column of HumanResources.JobCandidate table