How can I sort a querry by relevance?

  • Thread starter Thread starter Teban
  • Start date Start date
T

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
 
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
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
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.
 
Back
Top