Preferred approach for producing ranked SQL results?

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)

---

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

---

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;