Record Count

[D_tunisia]

I've got a table which records terms people have entered into my search engine. It's a very simple table with only two fields, ID and Text.

I want to give an output of the top 10 search terms but I can't work out how to do this.

For example if there are 5 searches for "caterpillar" annd only 2 for "fish" I want to display them in that order.

Thanks!

[timus]

Probably not the best way to do it but if you add a quantity field and set them all to 1 then you can do

select searchterm, sum(quantity) as quantitysum from yourtable group by searchter, order by quantitysum DESC

Really you need to put the quantity field in the table and have it update that record every time someone searches for an existing term. That would keep the size of the database smaller, and reduce repeating data

[TheDefender]

Something like the below query should do the trick...

SELECT text, count(text) as SearchCount GROUP BY text ORDER BY SearchCount DESC

(Of course, this won't account for misspellings or word conjugations such as house and houses. If one user enters caterpiller and another enters caterpillar, they will both be counted once, even though it's pretty obvious they both wanted the same search results. This kind of thing is hard, if not impossible, to account for though.)