Count * and order by

i have the following count query, which returns the number of records found for each studentid, however it would be really cool if i could order these in desc order by the total count, so that i can see the students at the top of a table with the highest count of records.

SELECT studentid, COUNT(*) as total FROM blue_form group by studentid


i tried adding an order by total desc on to the end of the query but it doesnt like it...

any other suggestions?

SELECT studentid, COUNT(*) as 'total' FROM blue_form group by studentid ORDER BY 'total' DESC


When ORDERing BY an alias, put the Alias in Single Quotes.

when i do that though i get an error when trying to write the total

using this to write out the total <% response.write rs1("total") %>

error i get is:

Source line: response.write rs1(total)
Description: Item cannot be found in the collection corresponding to the requested name or ordinal.

If you are using MSSQL or MSAccess, then try this:

SELECT studentid, COUNT(*) AS [total] FROM blue_form group by studentid ORDER BY 'total' DESC

Only leave the single quotes around the ORDER BY rather than both alias and ORDER BY.

I run a very similar query in MySQL all the time and it always works, so it must be an issue with MSSQL or MSAccess not seeing the alias as a field name in your results.

k, i can display the total with the order by in however the list is not ordered...

SELECT studentid, COUNT(*) as [total] FROM blue_form group by studentid order by 'total' asc



using this to write out the total: <% response.write rs1("total") %>

i am using access btw.

Would you be willing to run this code snippet?


<%
arrayTotals = rs1.GetRows()
RowCount = ubound(arrayTotals,2)
'
rs1.close
'
for x=0 to RowCount
response.write("Student: " & arrayTotals(0,x) & " :: Total: " & arrayTotals(1,x) & "<br />" & vbCrLf)
next
'
response.flush
%>


The above will tell you for sure if you are ordering in your SQL or not. It may be as simple as your <% response.write rs1("total") %> is not in the while not...wend loop.

nope, the totals are no where near in order!

this is the query: rs1.open "SELECT studentid, COUNT(*) as [total] FROM blue_form group by studentid ORDER BY 'total' DESC", DB

In Access, can you run the same query and see what you get?

If it won't run correctly, use Access' Query Wizard (or drag 'n' drop GUI) to build the query. Then, do a VIEW > SQL, and copy/paste from Access to your ASP.

It should work flawlessly from there.

Access has issues with aliases that are not exactly formed as it wants them.

this is what access gave me and it works!

SELECT blue_form.studentid, Count(*) AS total FROM blue_form GROUP BY blue_form.studentid ORDER BY Count(*) DESC


can you suggest how i limit it to 20 records, tried putting limit 20 on the end of DESC but it moans, also tried SELECT TOP 20 but oddly it gives me 23 records...

Well, adding the LIMIT syntax on the end is correct. Why it's moaning, I can't tell you.

Try the same Access trick again and add the LIMIT. See what it gives you.

Side Logic: If SELECT TOP 20 = 23 records, why not try SELECT TOP 17? :)