Solved

Classic ASP - group multiple keywords in database by popularity

Posted on 2010-09-10
37
515 Views
Last Modified: 2012-05-10
My database has entries "john doe" "john" "chicago" "new york" "new hampshire" in table "keywords". Each entry in it's own row.
I want to display these entries by popularity like:

2x john
2x new
1x doe
1x chicago
1x york
1x hampshire

How to do it? Thanks
0
Comment
Question by:matija_
  • 12
  • 11
  • 7
  • +2
37 Comments
 
LVL 3

Expert Comment

by:FreakyEddie
Comment Utility
Is the devider a space? i.e. Should an entry with two words, with a space in between, be seen as two seperate keywords?

Do you want it to be a solution in SQL or in ASP?
0
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
I would probably use the TOP 100 or something like that and then sum them by number of Occurrance.

For instance, let's say that you have a field (and I think you should) called Occurrance on your table.

Then during insert statement, insert initial record, and then increment the count anytime same name is inserted, you increment like:

DIM intCount
intCount = objRS("Occurrance")
objRS("Occurrance") = intCount + 1

SQL = "SELECT TOP 100 name, SUM(Occurrance) as Occurrance " & _
"FROM YourTable & _
"GROUP BY name " & _
"ORDER BY SUM(Occurrance) DESC"

Then that will give you the result.

You will use your UI to present it as 2x John, etc

Hope this helps
0
 
LVL 6

Author Comment

by:matija_
Comment Utility
@FreakyEddie
Yes, divider is space and entry with two or more words should be seen as separate keywords. It doesn't matter whether solution is in VbScript or SQL query.

@sammySeltzer
Thanks for suggestion, but there are several thousands of records already written to database, and now I'm trying to deal with that.
0
 
LVL 13

Expert Comment

by:Surone1
Comment Utility
if you write all the keywords seperately to another table first it makes things much easier.
you coud then "select distinct keyword, count(keyword) from table group by keyword order by keyword desc"
0
 
LVL 13

Expert Comment

by:Surone1
Comment Utility
"select distinct keyword, count(keyword) as mycount from table group by keyword order by keyword desc"
0
 
LVL 6

Author Comment

by:matija_
Comment Utility
@Surone1
I am aware of that, thanks, but as I wrote, keywords are already written.

So far I've managed to pull out all keywords from database, separate them by space, place in array, but I cannot count occurances in that array :( Hence my question, there might be an easier way of doing this, while preserving the database structure and records already written.
0
 
LVL 13

Expert Comment

by:Surone1
Comment Utility
use a temporary table?
0
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
I think the concept is the same.

SQL = "SELECT count(name) as Occurrance " & _
"FROM YourTable & _
"GROUP BY name " & _
"ORDER BY count(name) DESC"

Then on the asp side of things, you can do

do while not rs.EOF
   fname= rs("name")
    else
     response.write "no name found"
  end if
 
      Response.Write"<Font size=""2""> x " & rs("fname") & " </font>"
rs.MoveNext
loop
rs.Close
set rs = nothing
objConn.close
set objConn = nothing


something like this.

not tested of course

0
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
sorry put <br> at end
Response.Write"<Font size=""2""> x " & rs("fname") & " </font><BR>"
0
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
again, another small mistake.

Remove the
  else
     response.write "no name found"
  end if
0
 
LVL 6

Author Comment

by:matija_
Comment Utility
@sammySeltzer
You've missed the point, I know how to code such simple script to count and group identical entries and display them, but I need the entries to be divided into separate keywords first and then count and group them.
I don't want to make new tables or anything. I wrote snippet that separates the keywords and put them all in one giant Array, but I'm stuck how to group them, hence the original question.
0
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
ok got it.
0
 
LVL 3

Expert Comment

by:FreakyEddie
Comment Utility
What's the max numbers of spaces you have within one field? Two, three or ten.

With two or three we can do it in a query, with 10 the query is gonna be a bit long.
0
 
LVL 6

Author Comment

by:matija_
Comment Utility
@FreakyEddie
Lets try with 3 spaces, I can easily modify the script if more than that. There is not limit in working example.
0
 
LVL 3

Expert Comment

by:FreakyEddie
Comment Utility
i'll do my best
0
 
LVL 3

Accepted Solution

by:
FreakyEddie earned 500 total points
Comment Utility
Got it for 2 keywords

To explain it a bit:

CHARINDEX(' ', strSingleKeyword) + 1, field3) = field4
CHARINDEX(' ', strSingleKeyword, fiel2 – fiel1) = field3
CHARINDEX(' ', strSingleKeyword) + 1) = field2
CHARINDEX(' ', strSingleKeyword) = field1

Hope you can get it for 3... If I have it I'll let you know
SELECT COUNT(*), field1 FROM

(



SELECT     SUBSTRING(strSingleKeyword, 0, CHARINDEX(' ', strSingleKeyword)) AS field1

FROM         tableKeywords

UNION ALL

SELECT     LTRIM(RTRIM(SUBSTRING(strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1, CHARINDEX(' ', strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1) - CHARINDEX(' ', strSingleKeyword)))) 

                      AS field1

FROM         (SELECT     LTRIM(RTRIM(strSingleKeyword)) AS strSingleKeyword

                       FROM          tableKeywords AS der_table1) AS derivedtbl_1

WHERE     (strSingleKeyword LIKE '%  %')

)

group by field1

ORDER BY field1

Open in new window

0
 
LVL 3

Expert Comment

by:FreakyEddie
Comment Utility
This should do the trick for 3 keywords.
SELECT     COUNT(*) AS Expr1, field1

FROM         (SELECT     field1

                       FROM          (SELECT     SUBSTRING(strSingleKeyword, 0, CHARINDEX(' ', strSingleKeyword)) AS field1

                                               FROM          tableKeywords

                                               UNION ALL

                                               SELECT     LTRIM(RTRIM(SUBSTRING(strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1, CHARINDEX(' ', strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1) 

                                                                     - CHARINDEX(' ', strSingleKeyword)))) AS field1

                                               FROM         (SELECT     LTRIM(RTRIM(strSingleKeyword)) AS strSingleKeyword

                                                                      FROM          tableKeywords AS der_table1) AS derivedtbl_1

                                               WHERE     (strSingleKeyword LIKE '%  %')) AS derived_table3

                       UNION ALL

                       SELECT     LTRIM(RTRIM(SUBSTRING(strSingleKeyword, CHARINDEX(' ', strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1) + 1, CHARINDEX(' ', strSingleKeyword, CHARINDEX(' ', 

                                             strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1) + 1) - CHARINDEX(' ', strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1)))) AS field1

                       FROM         (SELECT     LTRIM(RTRIM(strSingleKeyword)) AS strSingleKeyword

                                              FROM          tableKeywords AS der_table1) AS derivedtbl_1_1

                       WHERE     (CHARINDEX(' ', strSingleKeyword, CHARINDEX(' ', strSingleKeyword) + 1) > LEN(strSingleKeyword))) AS derived_table4

GROUP BY field1

ORDER BY field1

Open in new window

0
 
LVL 3

Expert Comment

by:FreakyEddie
Comment Utility
Perhaps these articles will help you:
SQLTips
WeAsk
 
It explains and shows examples of the CharIndex function in SQL
 
Good luck!
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Since you are using MS SQL Server you may want to consider using Full-Text Search.
0
 
LVL 6

Author Comment

by:matija_
Comment Utility
I'm using MS Access database :\
0
 
LVL 3

Expert Comment

by:FreakyEddie
Comment Utility
The query didn't work out for you?
0
 
LVL 6

Author Comment

by:matija_
Comment Utility
@FreakyEddie
I have rewritten MSSQL string functions to Access equivalent, but the query is inscrutable to me. What is "strSingleKeyword"? My table has only 1 column named "keywords" which should match "field1" from your query.
0
 
LVL 3

Expert Comment

by:FreakyEddie
Comment Utility
strSingleKeyword is the name of the field.
tableKeywords is the name of the table.

So if you change strSingleKey to the fieldname of your own Database and change tableKeyword to the name of your table it should work.
0
 
LVL 6

Author Comment

by:matija_
Comment Utility
I have converted it to Access syntax, but it's not counting right - can you take a look to my query please:

SQL = "SELECT Keyword, COUNT(*) FROM (SELECT MID(strSingleKey, 1, INSTR(strSingleKey,' ')) AS Keyword FROM tableKeywords "
SQL = SQL & "UNION ALL SELECT LTRIM(RTRIM(MID(strSingleKey, INSTR(strSingleKey,' ') + 1, INSTR(INSTR(strSingleKey,' ') + 1, strSingleKey, ' ') - INSTR(strSingleKey,' ')) )) AS Keyword "
SQL = SQL & "FROM (SELECT LTRIM(RTRIM(strSingleKey)) AS strSingleKey FROM tableKeywords) WHERE (strSingleKey LIKE '%  %')) "
SQL = SQL & "GROUP BY Keyword ORDER BY COUNT(*) DESC"
0
 
LVL 3

Expert Comment

by:FreakyEddie
Comment Utility
There's a double space between %  % in WHERE (strSingleKey LIKE '%  %')) "
0
 
LVL 3

Expert Comment

by:FreakyEddie
Comment Utility
The Where-clause is only there because it will return an error when you substring a string which doesn't have a spece. So you should also include the strings in it which don't have spaces.

Use another substring for that like
Select Keyword FROM tableKeywords WHERE NOT(strSingleKey LIKE '% %')
UNION ALL
other substrings

or something like that
0
 
LVL 6

Author Comment

by:matija_
Comment Utility
I've left double space in "LIKE '%  %'" because it return error otherwise.
I think the problem lies within this line:

SQL = SQL & "UNION ALL SELECT LTRIM(RTRIM(MID(SearchTerm, INSTR(SearchTerm,' ')+1, INSTR(SearchTerm, ' ', INSTR(SearchTerm, ' ')+1) - INSTR(SearchTerm, ' ') ))) AS Keyword "

I can get the results, but it's counting wrong. Eg. I have the follwing rows:
"new york"
"new"
"york"
"york new"
"new york new"

and it returns:
2x "new"
2x ""
1x "york"

I'm a bit lost in this query :(
0
 
LVL 3

Expert Comment

by:FreakyEddie
Comment Utility
check this site.
this ones a bit better explained than mine, but it uses - in stead of spaces:

http://stackoverflow.com/questions/630907
But i think you'll manage.
0
 
LVL 6

Author Comment

by:matija_
Comment Utility
Thanks for all the inspiration @FreakyEddie, I've managed to rewrite your code snippet (using small hacks) to work flawlessly with MS Access database.

Final code:
SQL = "SELECT Keyword, COUNT(Keyword) FROM ("
SQL = SQL & "SELECT strColumnName AS Keyword FROM strTable WHERE NOT strColumnName LIKE '% %' "
SQL = SQL & "UNION ALL "
SQL = SQL & "SELECT MID(strColumnName, 1, INSTR(strColumnName, ' ')) AS Keyword FROM strTable WHERE strColumnName LIKE '% %' "
SQL = SQL & "UNION ALL "
SQL = SQL & "SELECT MID(strColumnName, INSTR(strColumnName, ' ') + 1, INSTR(INSTR(strColumnName + ' ', ' ') + 1, strColumnName + ' ', ' ') - INSTR(strColumnName + ' ', ' ')) AS Keyword FROM "
SQL = SQL & "(SELECT strColumnName AS strColumnName FROM strTable WHERE strColumnName LIKE '% %') "
SQL = SQL & ") GROUP BY Keyword ORDER BY COUNT(Keyword) DESC"

It's parsing only first 2 words for now, but with easy modification, more words can be added. Hope it helps someone in the future.
0
 
LVL 13

Expert Comment

by:Surone1
Comment Utility
you should really award the points to freakyeddie :-)
0
 
LVL 6

Author Comment

by:matija_
Comment Utility
Why 0 points to FreakyEddie's comment #33647969 when I selected 500pts x A multiplier?
0
 
LVL 13

Expert Comment

by:Surone1
Comment Utility
perhaps something went wrong?
0
 
LVL 13

Expert Comment

by:Surone1
Comment Utility
at least your intentions are clear, as soon as a zone advisor shows up they can close it correctly
0
 
LVL 6

Author Comment

by:matija_
Comment Utility
I have chosen FreakyEddie's answer as Best Solution which solved the question, entered 500 pts and chose A grade multiplier, but it says 0 points awarded when I close it... wtf?
0
 
LVL 13

Expert Comment

by:Surone1
Comment Utility
dont worry you supplied all the information they need to fix it. it will just take a while longer now.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>I'm using MS Access database :\<<
Which of course begs the question as to why you would accept a solution that is unusable as is in MS Access.
0
 
LVL 6

Author Comment

by:matija_
Comment Utility
@acperkins
It only begs the question if you hadn't read my final comment #33664458. I have awarded the FreakyEddie's comment as it has inspired me to rewrite his MSSQL solution into MS Access, which I have shared with the rest of you. I wanted to accept my own comment #33664458 as assisted solution (with no points given of course), but the EE bugged at that point giving no points to FreakyEddie.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now