Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-11-09 : 08:48:04
|
| Steph writes "I have a search page (vb.net windows form) that allows the user to search by any number of criteria (between 1 - 12 fields). I am using the COALESCE function (thanks to one of your fantastic articles that described how to do this). My question is, is there a way to return the results sorted by relevance. In other words, if the user passes in 4 paramters, can I return first any records that matched all 4 parameters, next any records that match 3 parameters, then 2, then 1? Basically, I want to rank the results in order of most relevant to least. I may have come up with a very slow, ugly work around in code, but I was wondering if there are any wonderful tricks that you may have to make this easier.Thanks so much for your time and your very useful web site.Stephp.s. using SQL Server 2000" |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-09 : 11:47:37
|
We do this with a CASE statement - because generally we want to give higher weighting to some parameters than others - for example if a Name is an exact match that's better thana partial match; a partial match for a whoel word is better than a "contained" word, and so onSELECT [Ranking] = CASE WHEN name = @NAME THEN 100 WHEN name LIKE @NAME + ' %' THEN 50 -- Whole word, starts with WHEN name LIKE @NAME + '%' THEN 40 -- Partial word, starts with WHEN ' ' + name + ' ' LIKE '% ' + @NAME + ' %' THEN 30 -- contains whole word WHEN name LIKE '%' + @NAME + '%' THEN 20 -- Contains matching string only ELSE 0 END + CASE WHEN age BETWEEN @MinAge AND @MaxAge THEN 25 ELSE 0 END -- Age is in range + CASE WHEN gender = @MatchGender THEN 15 ELSE 0 END -- Gender matches... Kristen |
 |
|
|
|
|
|
|
|
Sorting by relevance when using COALESCE