Preferred approach for getting ranked SQL results?
I need to build some search logic that gives ranked results. A simple example would be to query a table named firstname, lastname, and zip code. The required result is a list of strings that match, in order of "degree" of match.
Ie, the top results will be those that match all three columns, followed by those that match only two, followed by one column.
Is there a recognized preference for, for example, indexing the full text across multiple queries and combining the results? I shouldn't do anything?
I realize this is all pretty vague and a little counter to the specifics of SO questions, but I'm looking for guidance on an approach rather than a "do my homework" type of assignment :-)
(SQL Server 2005 platform)
source to share
Here is a quick sql solution that fits your 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;
source to share
Full Text Indexing will definitely give you this: you can query the FTS (Full Text Server) with CONTAINSTABLE
or FREETEXTTABLE
and get a list of possible matches, including their rank / relevance.
Check out the excellent articles for logging into SQL Server Full Text Search on Simple Talk:
as a good starting point.
Mark
source to share