Order results based on number of conditions

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 2000 Forums

Transact-SQL (2000)

Order results based on number of conditions

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2006-04-12 : 13:13:24

Hi there,

I have a where clause that has multiple 'like' conditions which are OR'ed together.

Is it possible to rank the results based on the number of conditions satisfied or is this full text index territory?

Cheers, XF.

Kristen
Test

22859 Posts

Posted - 2006-04-12 : 13:22:06

We add a rank column in such circumstances. For example, say I am looking for a column containing "FOO". I have a higher Rank if I get an exact match, next would be a "Starts With FOO" and lastly "Contains FOO"
[code]
SELECT MyColumn1, MyColumn2, ...,
[RANK] = CASE WHEN MyColumn3 = 'FOO' THEN 100
WHEN MyColumn3 LIKE 'FOO%' THEN 50
WHEN MyColumn3 LIKE '%FOO%' THEN 10
ELSE 0
END
+ CASE WHEN ... add more values for other "relevance" checks ...
FROM MyTable
WHERE MyColumn3 LIKE '%FOO%'
OR ... other tests ...
ORDER BY [Rank] DESC
[code]
Kristen

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2006-04-12 : 17:34:26

OK, and then the WHERE clause would just be something like...

WHERE RANK > 0

...right?

Do you know if full text indexes make doing this kind of stuff easier?

Kristen
Test

22859 Posts

Posted - 2006-04-13 : 01:36:23

"WHERE RANK > 0"

No because:

a) The where clause has to select relevant items, which can then have a RANK calculated
b) Because RANK is a calculated column in the SELECT and can't be used in the RANK

My idea is that you select WHERE ConditionA or ConitionB etc. Then you use Rank to calculate how "good" the match is.

"Do you know if full text indexes make doing this kind of stuff easier?"

Well you have less control that's for sure, although some of it you may not be able to control anyway. For example MyColumn3 containing "This does not work with FOO" would get a rank of 10 in my example above!

We hand code this type of ranking in order to be able to fine tune it - e.g. adding in popular misspellings, in-house synonyms and so on. But the Full Text stuff works well, albeit with some caveats and compromises.

Kristen

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2006-04-16 : 16:45:14

quote:
Because RANK is a calculated column in the SELECT and can't be used in the RANK

Indeed. It said 'Invalid column name' when I tried it.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-16 : 16:52:50

you can

select * from
(
SELECT MyColumn1, MyColumn2, ...,
[RANK] = CASE WHEN MyColumn3 = 'FOO' THEN 100
WHEN MyColumn3 LIKE 'FOO%' THEN 50
WHEN MyColumn3 LIKE '%FOO%' THEN 10
ELSE 0
END
+ CASE WHEN ... add more values for other "relevance" checks ...
FROM MyTable
WHERE MyColumn3 LIKE '%FOO%'
OR ... other tests ...
) a
WHERE RANK > 0
ORDER BY [Rank] DESC

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2006-04-17 : 10:15:51

It would surely be quicker to do it Kristen's way though because its only going to apply the case statement LIKEs to rows which definitely have the keyword in.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources