Best Match Order - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Best Match Order

Author

Topic

gagankhurana
Starting Member

8 Posts

Posted - 2007-02-28 : 10:46:44

Hi All
I have to search database on the basis of some parameters. I want to get all those records those have any one matching criteria. I know it is a simple query. But my requirement is "I want the result set in "best match order" i.e. if search criteria has 5 parameters, then all those records those have all criteria should come first then with less match(e.g. 4 parameters matching) and so on...
It is somewhat like google search as best matched results are coming first then less match and so on..
Please help me, if any one has any idea about it...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 11:17:53

[code]
select TOP 1 WITH TIES
Col1, Col2, Col3, Col4, Col5,
case when col1 = @param1 then 1 else 0 end +
case when col2 = @param2 then 1 else 0 end +
case when col3 = @param3 then 1 else 0 end +
case when col4 = @param4 then 1 else 0 end +
case when col5 = @param5 then 1 else 0 end as ColSum
FROM {YourTableNameHere}
ORDER BY 6 DESC[/code]
Peter Larsson
Helsingborg, Sweden

gagankhurana
Starting Member

8 Posts

Posted - 2007-02-28 : 11:32:24

quote:

Originally posted by Peso


select    TOP 1 WITH TIES
          Col1, Col2, Col3, Col4, Col5,
          case when col1 = @param1 then 1 else 0 end + 
          case when col2 = @param2 then 1 else 0 end + 
          case when col3 = @param3 then 1 else 0 end +
          case when col4 = @param4 then 1 else 0 end +
          case when col5 = @param5 then 1 else 0 end as ColSum
FROM      {YourTableNameHere}
ORDER BY  6 DESC

Peter Larsson
Helsingborg, Sweden

Thanks a lot Peter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources