How can I sort a querry by relevance?

[Teban]

How can I sort a querry by relevance?

Hi there
I have some ASP scripts that do a search in an Access
database table.
I want to be able to return rows that match a query, in
order of relevancy.
For example, say I have a table that has the following
data

|row_id| data
|------|-------------------------------------
| 1 | cat
| 2 | cat cat
| 3 | cat cat cat
| 4 | cat cat
| 5 | cat cat cat cat cat cat cat
| 6 | cat
| 7 | cat cat cat cat
| 8 | cat cat cat cat cat

I want to do a search that would return row_id 5 first,
then

row_id 8, then row_id 7, then 3, etc.
So I want to order the return by relevancy.
What should the sql query look like?
select * from table where data like '%cat%' order by ?????

thanks
-Teban

 

[Tom Ellison]

Dear Teban:

I would suggest you create a function that counts the references and
returns the score.

How can I sort a querry by relevance?

Hi there
I have some ASP scripts that do a search in an Access
database table.
I want to be able to return rows that match a query, in
order of relevancy.
For example, say I have a table that has the following
data

|row_id| data
|------|-------------------------------------
| 1 | cat
| 2 | cat cat
| 3 | cat cat cat
| 4 | cat cat
| 5 | cat cat cat cat cat cat cat
| 6 | cat
| 7 | cat cat cat cat
| 8 | cat cat cat cat cat

I want to do a search that would return row_id 5 first,
then

row_id 8, then row_id 7, then 3, etc.
So I want to order the return by relevancy.
What should the sql query look like?
select * from table where data like '%cat%' order by ?????

thanks
-Teban

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Sidney Linkers]

Hi,

What is it exactly what you mean by relevancy? (the lenght of data, the
count of "cat"). Is "cat" just to disguise the real data from us or do you
really have "cat" 1 or more times as a textvalue in the datafield seperated
by space? Is data a field in a table or some populated version so you
wouldn't have to type so many rows?

If relevancy is just just the lenght of the textvalue in the datafield then
you could try ORDER BY Len(Data) DESC.

Sid.