you don't need to do a self join. just do a
SELECT TOP 10 searchstring, count(searchstring) as thecount
FROM
<tablename>
GROUP BY
searchstring
ORDER BY count(searchstring) DESC
christopher olive, cto, vp of web development
cresco technologies, inc
http://www.crescotech.com
-----Original Message-----
From: Alistair Davidson [mailto:[EMAIL PROTECTED]]
Sent: Thursday, October 25, 2001 8:10 AM
To: CF-Talk
Subject: RE: SQL question grouping, ordering, sorting
Willy
Try something like -
SELECT TOP 10
(table).searchstring,
'NoSearches' = COUNT( tableAlias.ID )
FROM (table) INNER JOIN (table) tableAlias ON
(table).searchstring=
tableAlias.searchstring
GROUP BY (table.searchstring)
ORDER BY COUNT( tableAlias.ID ) DESC
( replace (table) with your table name )
That should do it - for each row in (table) it will return all rows with
the
same searchstring, and the COUNT function used with the GROUP BY clause
will
'roll-up' the results
HTH
Alistair Davidson
Senior Developer
Rocom New Media
www.rocomx.net
"There Is No Spoon"
-----Original Message-----
From: Willy Ray [mailto:[EMAIL PROTECTED]]
Sent: 22 October 2001 16:07
To: CF-Talk
Subject: SQL question grouping, ordering, sorting
Ok, I have a database of search strings with which users of the site
have
hit my search engine. I want to build a viewer so I can go in and see
what people are doing without actually cracking into the database. My
database isn't doing anything fancy. It's one table, consisting,
essentially, of id, searchstring, casesensitive...
What I want is to be able to query the database, and display the 10 or
15
most searched strings. I'm just not sure how to go about it.
I can
GROUP BY searchstring
that get's me close, and culls out duplicate strings, but I don't know
how
I'd get from there to
top 5 strings:
1. Happy and Go and Lucky (16 searches)
2. "Moo Cow" (10 searches)
3. etc.
4. etc.
Any thoughts?
Willy Ray
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
