building an sql query with javascript and ASP

[colinod]

I am trying to build a sql query for an exam site and need to list the top scoring exams for a user, there can be multiple exams.

I have the following script in ASP that puts the results into a javascript function so they can be read by a cms system on another server.

rs.Source = "select ID, Username, UserID, Test_Name, MAX(Grade) FROM testscores WHERE UserID = " & Session("sesUserID") & " AND Grade > 69 GROUP BY UserID,Test_Name ORDER BY CAST(Grade AS UNSIGNED)"
 
 
Response.write("moduleCertificates: function(){")
response.write(vbcrlf)
response.write ("return {")
 
rs.Open()    
while not rs.eof
 
Dim testName_Short
Dim testName
 
testName = rs.Fields.Item("test_Name").Value
testName_Short = testName
testName_Short = replace(testName_Short, "Gold_", "")
testName_Short = replace(testName_Short, "Silver_", "")
testName_Short = replace(testName_Short, "Bronze_", "")    
 
response.write("'" & testName_Short & "': ' https://testwww.sharpacademy.eu/certificate.swf?UID=" & rs.Fields.Item("UserID").Value &"&ID=" & rs.Fields.Item("ID").Value & "',")
response.write(vbcrlf)
 
rs.MoveNext
Wend
response.write ("};")
response.write(vbcrlf)
response.write ("}")
response.write(vbcrlf)
rs.Close
Set rs = Nothing
 

this returns one result per exam for the user but it is the first one in the database for the exam and user.

i only need to return the ID and UserID from the database.

Any help would be appreciated

[Rabbit]

You're saying that query runs? I don't see how that's possible, there are syntax errors. You have fields in your SELECT that aren't in your GROUP BY or in an AGGREGATE function.

[colinod]

as far as i am aware i can group by 1 field, why would you want to group by multiple fields

[Rabbit]

You can group by 1 field if you use the rest of the fields in an aggregate function.

You would want to group by multiple fields if those fields represent the unique combination that you want to aggregate by.

When you do an aggregate query, every field in the SELECT has to be either in a group by or in an aggregate function. Otherwise, the database has no idea what value to select for the rest of the fields.