selecting top 5 searches

[dotolee]

hi there. i'm trying to write some sql code that will search for the top 5 most frequently appearing items in a log file. The field I'm looking for is the top 5 "searchText" values.
Right now, my code looks like this:
set objCnn = Server.CreateObject("ADODB.Connection")
objCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & logfilepath & "Extended Properties=""Text;"""

SQL = "SELECT TOP 5 SearchText, COUNT(*) AS howMany " _
& " FROM " & logfileName _
& " WHERE SearchStatus='SUCCESS' " _
& " ORDER BY COUNT(*) DESC"
Set RS = objCnn.Execute( SQL )
Do Until RS.EOF
Response.Write RS("howMany") & " searches for " & RS("SearchText") & "<br/>"
RS.MoveNext
Loop

But it's failing with error 80040e21 - you tried to execute a query that does not include the specified expression "SearchText" as a part of the aggregate function.
I tried adding SearchText to the ORDER BY clause just before the COUNT(*) but that dosen't seem to work either.
The other question I have is how do i change the above sql to return all fields, not just the search text and the count?
Thanks

 

[PHV]

I'd try this:
SQL = "SELECT TOP 5 SearchText, COUNT(*) AS howMany " _
& " FROM " & logfileName _
& " WHERE SearchStatus='SUCCESS' " _
[!] & " GROUP BY SearchText" _[/!]
& " ORDER BY COUNT(*) DESC"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886