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

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

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

How to know whether a computer is a Sql Server according to the name of computer

Thanks a lotremark: in sql statement|||run this query:select srvproduct from dbo.sysservers
if it comes back with 'SQL Server' then you are on SQL Server|||SELECT SERVERPROPERTY('SERVERNAME')
It would return the Instance name. If the output resembles with ur system-name; the SQL is the default instance. Othwerwise it would be a named instance, i.e., like :machine\instance|||such as :
you are on computer A that running a sql server,then you want to konw if computer B is running sql server.|||Download this tool from Microsoft:

http://www.microsoft.com/downloads/details.aspx?FamilyID=9552d43b-04eb-4af9-9e24-6cde4d933600&DisplayLang=en

It contains SQLScan, which will scan the LAN for all SQL Server installations.|||If you have sql server, and you are interested:

select @.@.version

will give you the version information.

:)