Getting frequency counts from a table

Mr. Bigglesworth · Post by **Mr. Bigglesworth** » Tue Oct 09, 2001 1:50 pm

A client has asked me to see if we can run a query against one of our texis tables, to find the most frequent terms in the database.

We have an all-fields metamorph index on the table, and would likely be looking for the top 100 terms in the index, along with a count of how often they appear. Any chance you could sketch out a query we might run to accomplish this? thanks.

Post by **Kai** » Tue Oct 09, 2001 2:58 pm

Set indexaccess=1, and you can select from an index as if it were a table. For a Metamorph index, you'll get Word and Count fields, which are the indexed word and the count of rows that it occurs in. You could order by Count desc to get the top N rows (may take a few seconds as there is no "index" on the Count "field"). Eg.:

tsql -l 100 "set indexaccess=1; select * from mymetamorphindex order by Count desc"

Mr. Bigglesworth · Post by **Mr. Bigglesworth** » Tue Oct 09, 2001 4:01 pm

Sounds like just what we need-- thanks!