ASP How to group keywords in a field and by row

I need an asp query that will group and count keywords in a field, such
as a description field, count them, go to the next row, do the same and
add them together by keyword and the return the list in descending
order.

I have an example below:

Part Description
199888-001 Compaq SCSI Hard Drive
5065-6731 HP CD-ROM
199466-001 Compaq SCSI 4/16GB

Results:

Compaq = 2
SCSI = 2
HP = 1
Hard = 1
Drive = 1
CD-ROM = 1
4/16GB = 1

Thanks for any help in advance.

Re: ASP How to group keywords in a field and by row

ayo991@gmail.com wrote:
> I need an asp query
??
I think you mean "sql query" ...

> that will group and count keywords in a field,
> such as a description field, count them, go to the next row, do the
> same and add them together by keyword and the return the list in
> descending order.
>
> I have an example below:
>
> Part Description
> 199888-001 Compaq SCSI Hard Drive
> 5065-6731 HP CD-ROM
> 199466-001 Compaq SCSI 4/16GB
>
> Results:
>
> Compaq = 2
> SCSI = 2
> HP = 1
> Hard = 1
> Drive = 1
> CD-ROM = 1
> 4/16GB = 1
>
> Thanks for any help in advance.

What database?
Type and version please.

Hmmm ... maybe you didn't mean "sql query" after all. I think you meant
"vbscript code".

Unless you define "keywords" very precisely, you will need a human being
to perform this task. So your first task is to decide how to get the
computer to decide between keywords and non-keywords. You have a choice
of creating a list of key words, or creating a list of words to ignore.
Decide which path you wish to take

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: ASP How to group keywords in a field and by row

wrote in message
news:1163528197.809962.183590@m73g2000cwd.googlegroups.com.. .
>I need an asp query that will group and count keywords in a field, such
> as a description field, count them, go to the next row, do the same and
> add them together by keyword and the return the list in descending
> order.
>
> I have an example below:
>
> Part Description
> 199888-001 Compaq SCSI Hard Drive
> 5065-6731 HP CD-ROM
> 199466-001 Compaq SCSI 4/16GB
>
> Results:
>
> Compaq = 2
> SCSI = 2
> HP = 1
> Hard = 1
> Drive = 1
> CD-ROM = 1
> 4/16GB = 1

The best would depend on how many keywords you wish to define (and/or
whether you want the list of keywords to be build dynamically from content)
and how often the description field value will be inserted or changed.

If you want to build the list of keys on the fly, and esp. if your next
requirement will involve locating entries that include a given keyword, I'd
suggest creating a keyword table, a noise word table and a crossreference
table. The keyword and xref tables will, of course, need to be maintained
each time a description is added or changes.

The basic flow is, split the field at each space and create a temp
table-valued object with each word of the description in a separate row.
(If using SQL, a table-valued function would be ideal; if not, you may end
up populating a temp table.) You group to make the rows unique, outer-join
to noise words (where noise.keyword is not null) to exclude them, outer join
to the existing keywords table (where existing.keyword is null) to avoid
duplication, and insert anything that's left into keywords.

To maintain the xref table, join the temp table-valued object to keywords,
selecting the keyword table's identity column, and the description's
record's identy as a derived column (presumably you'd read it at the same
time you read the description) and insert the results into the xref table.

You end up with a table that has exactly one row for every keyword that
ocurs, and another table that links each occurrence of each keyword with the
description records in which they occur. From there, the sky is the limit,
you can count occurrences per row or across the table, you can count
descriptions that include given keywords, and you can provide very fast
search capabilities.

The drawback to the scheme is keyword/xref maintenance overhead. I've
deployed this scheme in several places; the one that I actually own is an
archive for a mailing list, that currently has about 36,000 posts archived,
containing ~93,000 unique keywords, with ~2,400,000 crossreferences.
Searches for words that occur under a couple thousand times return in a
sub-second. Searches for words that many times incur some i/o time, but are
still very respectable. The scheme as a whole tends to scale fairly well.


Good Luck,
Mark



> Thanks for any help in advance.
>