Help with query.

[bforce]

Need help with query. I want to find the most recurring search term in a
database dynamically. I have the following but it is based on a hardcoded
value and the count being greater than 1:

<cfquery name="MYQUERY" datasource="MYDS">
Select productkeyword from searchStats
Where productkeyword = 'boiler'
GROUP BY [productKeyword]
HAVING Count(*)>1
</cfquery>

I would like for this query to be dynamic. Help greatly appreciated.

[Mike Greider]

How about something like (the code isn't right, but the idea might work) ...

...
HAVING count(*) > select max(count(*)) from searchStats where productkeyword =
'boiler'

Don't know if you can nest functions like that, but something along those
lines might work.

[bforce]

Got it!

<cfquery name="MYQUERY" datasource="MYDS" MAXROWS="5">
SELECT keyword, COUNT(*) AS TERM
FROM searchStats
WHERE (keyword is not null)
GROUP BY keyword
ORDER BY COUNT(*) DESC
</cfquery>

Returns the top 5 search terms and how many times they were searched on.





"bforce" <bforce@poultryegg.org> wrote in message
news:d6i4t3$lbi$1@forums.macromedia.com...

> Need help with query. I want to find the most recurring search term in a
> database dynamically. I have the following but it is based on a hardcoded
> value and the count being greater than 1:
>
> <cfquery name="MYQUERY" datasource="MYDS">
> Select productkeyword from searchStats
> Where productkeyword = 'boiler'
> GROUP BY [productKeyword]
> HAVING Count(*)>1
> </cfquery>
>
> I would like for this query to be dynamic. Help greatly appreciated.
>

[CF_fdk]

Maybe you can limit the number of records on database level in MSSQL server this is 'SELECT TOP 5 Column FROM TABLE ...'