sorting problem [Archive]

styleboy

07-28-02, 14:32

I'm running an access database with asp on my website:
http://www.styleboost.com

I've added a rating system, which counts the number of votes so far (+1 for each vote) and adds up a total rating (current total + (1-5)). This means that there are two columns in my table (rating_sum, rating_votes) and I want to be able to sort by:
[rating_sum]/[rating_votes] (rating_sum divided by rating_votes).

How can I do this? Please help me...

Johan, Norway

r937

07-28-02, 15:06

you were so close... select url, screenshot, title
, CASE
WHEN rating_votes = 0 THEN 0
ELSE rating_sum / rating_votes
END as rating_avg
from yourtable
order by rating_avg descendingthis gives unrated entries a rating of 0

if your database doesn't support CASE, you can also do this with a UNION -- holler if you need it

rudy
http://rudy.ca/

styleboy

07-28-02, 16:01

thanks ... but it didn't work. At least that's what I think, but I'm no asp guru. ;-) This is what my code looked like before:

Dim oRS, oCOM
Set oRS = Server.CreateObject("ADODB.Recordset")
Set oRS.ActiveConnection = oConn
oRS.CursorLocation = 3
oRS.PageSize=10
oRS.Open "select * from ref_Site order by " & strSort,,3,1

What should it look like?

Johan

Originally posted by r937
you were so close... select url, screenshot, title
, CASE
WHEN rating_votes = 0 THEN 0
ELSE rating_sum / rating_votes
END as rating_avg
from yourtable
order by rating_avg descendingthis gives unrated entries a rating of 0

if your database doesn't support CASE, you can also do this with a UNION -- holler if you need it

rudy
http://rudy.ca/

r937

07-28-02, 16:33

what should it look like? sorry, i don't do asp

you were asking about sorting, and that's best done in the sql

i think all you have to do is plug the sql i gave you into your asp code where your current query is, except don't concatenate the strSort variable...

rnealejr

07-28-02, 20:14

Access does not support "case when". Anyway, try the following:

select field1, field2, ratingsum/ratingvotes from table order by 3

You can still alias your ratingsum/ratingvotes but you can not use that alias in your order by statement - you need to use the ordinal of the location within the select statement. If you do not alias, use the ratingsum/ratingvotes in your order by statement.

r937

07-28-02, 20:50

Access does not support "case when"yeah, i forgot -- access has always had IIF(), which is uglier but it works

hey, at least i remembered not to divide by zero, and to sort descending select url, screenshot, title
, IIF(rating_votes=0, 0,
rating_sum / rating_votes)
as rating_avg
from yourtable
order by 3 descending:cool:

styleboy

07-30-02, 16:42

Thanks guys. It finally works! It'll be online by the end of the day.

styleboy

07-30-02, 16:54

Thanks guys. It finally works! It'll be online by the end of the day.