How to sort an output of a simple asp output

[TheQuestor]

I just can't get my head to grasp how to do this

I have my sql setup, and in it a number of skins. The skins are are able to be ranked and I need to be able to sort the skins by the rank.

I'm needing to redesign my Skins page here http://www.sshcs.com/xbmc/?mode=Skins&TM=All


and my test function output is here.
http://www.sshcs.com/xbmc/inc/eva.asp?mode=Learn


output is this

Aeon - Rank is 8.4
MediaStream - Rank is 8.33333333333333
PM3HD - Rank is 6.28571428571429
PMIII - Rank is 5.75
Serenity - Rank is 4.625

The code to get this is this.


Function Learn()
Set rsSkinNames = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT DISTINCT(Skin) FROM SkinRank"
rsSkinNames.Open strSQL, adoCon
'returns distinct skin names

Do Until rsSkinNames.EOF
TheIP = rsSkinNames("Skin")
Response.Write (""&TheIP&" - ")


Set rsRankCount = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT COUNT(Rank) AS RankCount FROM SkinRank WHERE Skin = '"&TheIP&"'"
rsRankCount.Open strSQL, adoCon
TheCount = rsRankCount("RankCount")
Set rsRankSum = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT SUM(Rank) AS RankSum FROM SkinRank WHERE Skin = '"&TheIP&"'"
rsRankSum.Open strSQL, adoCon
TheSum = rsRankSum("RankSum")

If IsNull(Int(TheSum)) OR IsNull(Int(TheCount)) Then
TheAverage = 0
Else
TheAverage = Int(TheSum) / Int(TheCount)
End If

Response.Write("Rank is "&TheAverage&" <br />")
rsSkinNames.movenext
Loop

rsSkinNames.Close
Set rsSkinNames=Nothing
rsRankCount.Close
Set rsRankCount = Nothing
rsRankSum.Close
Set rsRankSum = Nothing
End function



I know it seems that it is already sorted, but that is just the luck of the output. When I add new skins and give them a rank higher or lower they all appear at the bottom such as

Aeon - Rank is 8.4
MediaStream - Rank is 8.33333333333333
PM3HD - Rank is 6.28571428571429
PMIII - Rank is 5.75
Serenity - Rank is 4.625
TestSkin1 - Rank is 2.5
TestSkin2 - Rank is 10

I am stuck on how to sort the output by the average rank.

As in I need the one with the hightest average rank at the top, and the lowest at the bottom.

I have never been able to understand arrays, but I "FEAR" that this is one time I am going to need to use one.

Please help!!!!!

[Old Pedant]

No, not time to understand arrays, at all.

Time to understand SQL.

You should do this all with *ONE* query. Period.


Sub Learn()
SQL = "SELECT Skin, AVG(1.0*Rank) AS RankAverage " _
& " FROM SkinRank GROUP BY Skin ORDER BY 2 DESC "
Set rsRank = adoCon.Execute( SQL )
Do Until rsRank.EOF
Response.Write "Skin: " & rsRank("Skin") _
& " averages " & rsRank("RankAverage") _
& "<br/>" & vbNewLine
rsRank.MoveNext
Loop
rsRank.Close
End Sub

I multiplied Rank by 1.0 to ensure you get a floating point number as the average. May or may not be necessary in the DB you are using.

You also may or may not be able to do "ORDER BY RankAverage DESC" (instead of using the ordinal field number) depending on what DB.

[TheQuestor]

No, not time to understand arrays, at all.

Time to understand SQL.

You should do this all with *ONE* query. Period.


Sub Learn()
SQL = "SELECT Skin, AVG(1.0*Rank) AS RankAverage " _
& " FROM SkinRank GROUP BY Skin ORDER BY 2 DESC "
Set rsRank = adoCon.Execute( SQL )
Do Until rsRank.EOF
Response.Write "Skin: " & rsRank("Skin") _
& " averages " & rsRank("RankAverage") _
& "<br/>" & vbNewLine
rsRank.MoveNext
Loop
rsRank.Close
End Sub

I multiplied Rank by 1.0 to ensure you get a floating point number as the average. May or may not be necessary in the DB you are using.

You also may or may not be able to do "ORDER BY RankAverage DESC" (instead of using the ordinal field number) depending on what DB.

Ended up using this
Set rsSkinNames = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT Skin, " & _
"COUNT(Rank) AS RankCount, " & _
"SUM(Rank) AS RankSum, " & _
"CAST(SUM(Rank) AS FLOAT)/CAST(COUNT(Rank) AS FLOAT) AS TheAverage " & _
"FROM SkinRank " & _
"GROUP BY Skin " & _
"ORDER BY TheAverage DESC"
rsSkinNames.Open strSQL, adoCon

Which did what I needed, but thank you for taking the time to help.

[Old Pedant]

Honest, doing

AVG(CAST(Rank) AS FLOAT) AS The Average

would give the same answer.

Or do the multiply by 1.0 only because it's less code.

Still same answer.