By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,867 Members | 1,981 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,867 IT Pros & Developers. It's quick & easy.

building an sql query with javascript and ASP

100+
P: 347
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.

Expand|Select|Wrap|Line Numbers
  1. 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)"
  2.  
  3.  
  4. Response.write("moduleCertificates: function(){")
  5. response.write(vbcrlf)
  6. response.write ("return {")
  7.  
  8. rs.Open()    
  9. while not rs.eof
  10.  
  11. Dim testName_Short
  12. Dim testName
  13.  
  14. testName = rs.Fields.Item("test_Name").Value
  15. testName_Short = testName
  16. testName_Short = replace(testName_Short, "Gold_", "")
  17. testName_Short = replace(testName_Short, "Silver_", "")
  18. testName_Short = replace(testName_Short, "Bronze_", "")    
  19.  
  20. response.write("'" & testName_Short & "': ' https://testwww.sharpacademy.eu/certificate.swf?UID=" & rs.Fields.Item("UserID").Value &"&ID=" & rs.Fields.Item("ID").Value & "',")
  21. response.write(vbcrlf)
  22.  
  23. rs.MoveNext
  24. Wend
  25. response.write ("};")
  26. response.write(vbcrlf)
  27. response.write ("}")
  28. response.write(vbcrlf)
  29. rs.Close
  30. Set rs = Nothing
  31.  
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
Jul 16 '14 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,370
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.
Jul 16 '14 #2

100+
P: 347
as far as i am aware i can group by 1 field, why would you want to group by multiple fields
Jul 18 '14 #3

Rabbit
Expert Mod 10K+
P: 12,370
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.
Jul 18 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.