EasyCFM.COM ColdFusion Forums / MS SQL Server Related Issues / QUERY CONNUNDRUM

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: QUERY CONNUNDRUM

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

tgruen
03-07-2011 @ 1:31 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 59
Joined: Aug 2006

Howdy. I am building a page and I am trying to envision the SQL query required. After spending hours in texts I thought I would post here.

I have a db table (MS SQL Server  2008) that records what search parameters are entered into our site. I am trying to create a page that shows the top 20 searches performed. Each time the website search is submitted I am recording what the visitor searched for in a database table named 'psearches' and the table column data field is 'sdata'.

I have tried various mixes of SELECT DISTINCT and COUNT but I don't have it right and my results are wack.

Does anyone have a suggection or hint? I am researching SQL query syntax but think I am on the wrong track. Any help is greatly appreciated.

Tony

Webmaster
03-07-2011 @ 9:50 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

I think we would need to know more... what you are
looking to do is usually handled via a index or a
verity; but we can certainly find a solution.

Depending on how you have your db table structured and
on what you actually want to perform the login on will
differ the query.

Are you trying to look for popular "single keyword",
"search strings", zipcodes, etc?

I will need to understand the base system a little
better to provide some guidance.

Let me know the DB table, what you log and what exactly
you are wanting to use as a "popular" term... let's get
that and go from there.

P

Pablo Varando
Senior Application Architect
EasyCFM.COM, LLC.

904.483.1457 \\ mobile
webmaster@easycfm.com \\email

\m/ (>.<) \m/
--- rock on ---

tgruen
03-07-2011 @ 10:16 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 59
Joined: Aug 2006

I am trying to keep it as simple as possible. Just a list of unique search phrases entered and number of time it aoppears in the db. So if some one searches the site for "poptart" 30 times in a month the page would show "poptart - 30".

I worked on it over the weekend and recreated the db table. it is only three fields

src_id = unique id
src_phrase = search phrase entered
src_datetime = time search performed

No verity, no index. The page will see little traffic so I am not performance tuning.

I can get a list of unique entries,
[SELECT DISTINCT (src_phrase) FROM searches]

I can count unique entries
[SELECT COUNT (DISTINCT src_phrase) AS counts FROM searches]

...but I am having trouble tieing them together.

I am going to try puilling the first list and looping ouver it with the second one today. Please post if you can think of another way.


Tony

This message was edited by tgruen on 3-7-11 @ 10:16 PM


Website Designed and Developed by Pablo Varando.