Preferred approach for producing ranked SQL results?

Question

Hi. I need to build some searching logic that produces ranked results. A simple example would be querying a table with firstname, lastname and zip code columns. The required output would be a list a rows that match, in order of the 'degree' of match.

I.e., the top results would be those that matched on all three columns, followed by those that only matched on two, followed by the single column matches.

Is there a recognised preference of say Full Text Indexing over multiple queries and combining the results? Is there anything I definitely shouldn't be doing?

I appreciate this is all rather vague, and slightly at odds with the specific nature of SO questions, but I'm looking for guidance on the approach to take rather than a 'do my homework' type question :-)

(The platform is SQL Server 2005)

Answer 1

Fulltext indexing will definitely give you this - you can query the FTS (fulltext server) with CONTAINSTABLE or FREETEXTTABLE and get a list of possible matches, including their rank / relevancy.

Check out the excellent intro articles for SQL Server Fulltext search on Simple Talk:

• Understanding Full-Text Indexing in SQL Server
• Full-Text Indexing Workbench

as a good starting point.

Marc

Answer 2

Here is a quick sql-solution that matches you question. After creating this function you can
ORDER BY dbo.func_getWeightByColumn(@firstname, firstname, @lastname, lastname, @zipcode, zipcode) DESC

CREATE FUNCTION [dbo].[func_getWeightByColumn] 
(
 @p1a varchar(50),
 @p1b  varchar(50),
 @p2a varchar(50) = 'a',
 @p2b  varchar(50) = 'b',
 @p3a varchar(50) = 'a',
 @p3b  varchar(50) = 'b',
)
RETURNS int
AS
BEGIN
 DECLARE @Result int
 SET @Result = 0

 IF @p1a = @p1b 
 BEGIN
  SET @Result = @Result + 1
 END

 IF @p2a = @p2b 
 BEGIN
  SET @Result = @Result + 1
 END

 IF @p3a = @p3b 
 BEGIN
  SET @Result = @Result + 1
 END

 RETURN @Result
END;