sorting problem

styleboy · #1 (**permalink**) 07-28-02, 13: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 · #2 (**permalink**) 07-28-02, 14:06

you were so close...

Code:

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 descending

this 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 · #3 (**permalink**) 07-28-02, 15: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

Quote:

Originally posted by r937
you were so close...

Code:

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 descending

this 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 · #4 (**permalink**) 07-28-02, 15: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 · #5 (**permalink**) 07-28-02, 19: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 · #6 (**permalink**) 07-28-02, 19:50

Quote:

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

Code:

select url, screenshot, title
     , IIF(rating_votes=0, 0,   
           rating_sum / rating_votes)
         as rating_avg
  from yourtable
order by 3 descending