ADVANCED ORDERING 1

[Silvinho]

I’m having a problem which I’m not sure is even achievable.
I’ve created a search results page that works normally i.e.:

strSearch = Request.Form(“search”)
SELECT * FROM Table1 WHERE Field1 LIKE ‘%strSearch%’

Say the form variable is ‘Dog’
What I want to achieve is to count the number of times the variable ‘Dog’ appears in the search.

The ultimate aim was to order the query by the number of times the variable appeared so this might not be the best way to do this but I cant think of anything better for now.

Anyway any help would very helpful.

 

[SQLBill]

Let's say you have a column called ANIMAL, you could do:

select distinct animal, count(animal) as amount
from mytable
where animal = 'dog'
group by animal
order by amount desc
go

That will give you the count of the number of times 'dog' appears in the animal column. If you leave out the WHERE clause, it will list all animals (but only once per animal type) and the number of times that animal appears in the column; then it will list it by the largest amount first.

For example:

animal amount
----------------------------
cat 27
elephant 20
ant 15
dog 7
zebra 5
horse 1


-SQLBill

 

[swampBoogie]

SELECT table1.*, 
length(field1) - length(replace(field1,'dog','')/
length('dog') as occurances
FROM Table1 
WHERE Field1 LIKE '%dog%'
order by occurances desc