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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
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
